2 Replies Latest reply: May 15, 2014 10:10 AM by Victoria _ RSS

    External Link

    Victoria _

      Hi, I was wondering how to do a cross join two tables in Monarch. I have two tables can only link the amount column. I would like to have the final table lists all the equal amount in both tables + a list of amount in  table1 (can’t find in table1) + a list of amounts in table2(can’t find in table1). 

       

      Thank you!

      Vic

        • External Link
          Grant Perkins

          Hi Victoria and welcome to the forum.

           

          I think you may need a multiple step process here (it can probably be automated to a single 'click' activity for operational purposes) to get the full analysis you are seeking, However I suspect that it may not be that simple since 'amouonts' tend not to be unique and therefore cannot be easily connected across data sources without some further information. And of course if you have the further information then your options for making connections are increased.

           

          I am wary that your source data may be different in this respect - the exception that breaks the rule - but if we are looking for a process that will work for you it will be important to appreciate the full picture.

           

          In general terms when I have dealt with similar 'exists in one file but not in another' comparisons in the past I have often resorted to na 'Master File' that creates all possible parent records that should or could, in theory, exist in both files and then added the lookup links to that. The result, in simplistic terms, will be a record from one file or the other file or both (in your scenario) and should give the basis for further analysis.

           

          Does that make any sense?

           

           

          Grant

            • External Link
              Victoria _

              Thanks for your message, Grant! : ) Sorry I didn’t describe it clearly earlier. The possible mutual columns in these two files that could match up are “date”, “description” and “amount”, all other columns are not relevant. “Date” column won’t match, because it will be either one day behind or one day earlier or the same date, so it is uncertain.      “Description” column in two files are totally different, I could list both description in the final file, but won’t match.  Like you said, “amount” column are not unique at all (one table could have five 1000). But all I want to do is to find the amount exist in both tables (files), and if those amount in table1 can’t be found in table 2, then list it(just the amount is enough), Vice versa. And those amounts will be matched in previous or next month’s reports later, and get it balanced.  So I was wondering how could I do this in Monarch, and even get it automated? 

               

              Talking about automation, I was wondering if I could ask another question. I have multiple reports using the same template and then export to one Excel file. In Excel I code:

               

              Openfile= MonarchObj.SetReportFile( File1……)

              Openfile= MonarchObj.SetReportFile( File2……)

              ……

              …..

              Openmode=…..

              Exporttable……..

               

              But with the code opening multi reports, sometimes it just hang there, and I have to run several times, then it would go through. If I  manually open each report in Monarch, I will have to check the box “Retain current Reports” every time I open a report. Can this “retention options” can be turned off? Then it will always open all the reports as default. Then my automation will work?  Or there is something wrong with my code?  

               

              Thanks again!

              Vic