2 Replies Latest reply: May 15, 2014 9:53 AM by fino _ RSS

    Lookup with a table

    fino _

      I have > 200 reports feeding into one model - all reports have identical layout. One Header field contains 2 different field values either "B" or "U". These values describe currency type of the amount fields. I need to build a file output that has both types "B" and "U". I guess what I am asking is to do a lookup in a table on itself - dont think this is possible but I am new to Monarch.

      Here are example input report lines

      B     1300     5     6

      B     1301     10     20

      U     1300     7.2     1.23

      U   :confused:        1301     6.5     4.56

       

      I would like to see a table view or output

      1300     5     6     7.2     1.23

      1301     10     20     6.5     4.56

       

      kind regards

      Fino

       

      [size="1"][ May 04, 2007, 05:03 AM: Message edited by: fino ][/size]

        • Lookup with a table
          Grant Perkins

          Originally posted by fino:

          I have > 200 reports feeding into one model - all reports have identical layout. One Header field contains 2 different field values either "B" or "U". These values describe currency type of the amount fields. I need to build a file /codeoutput that has both types "B" and "U". I guess what I am asking is to do a lookup in a table on itself - dont think this is possible but I am new to Monarch.

          Here are example input report lines

          [font="courier"]B     1300     5     6

          B     1301     10     20

          U     1300     7.2     1.23

          U       1301     6.5     4.56[/font][/quote]I would like to see a table view or output

          [font="courier"]1300     5     6     7.2     1.23

          1301     10     20     6.5     4.56[/font][/quote]kind regards

          Fino /b[/quote]Hi fino and welcome to the forum.

           

          Is it safe to assume that the 3rd and 4th columns in you sample also have headers to identify what they are?

           

          If they do that would be useful. If I assume they do for now that would give 4 columns in total: Currency, Reference Group, Value1 and Value2.

           

          Your result needs to be something like:

           

          Reference Group (aggregate), Curr B/Value1, CurrB/Value2, CurrU/Value1, CurrU/Value2 .

           

          I could see the solution as either a summary with that format OR a 2 stage process which extracts all the U values (for example) to an external lookup file and then re-runs picking up the B values and adding the U values using the Reference Group code as the key.

           

          The summary seems a better option based on the sample so far. Providing the 3rd and 4th columns havce a column heading!

           

          If you use multi-column region concept and set the template to 2 columns that will grab the 3rd and 4th columns that will extract your core detail data.

           

          Trap the Currency Code and Reference Code fields in an APPEND template.

           

          Now the important assumption. Trap the value column headers in another append template. (Filed called VALUE from here on).

           

          If this works with the real report you will have a table something like this;

           

          [font="courier"]B     1300     Value1       5.00     

          B     1301     Value1      10.00

          U     1300     Value1       7.20

          U       1301     Value1       6.50

          B       1300    Value2       6.00

          B       1301    Value2      20.00

          U       1300    Value2       1.23

          U       1301    Value2       4.56[/font][/quote]Now in the table create a calculated field that sets a unique value for each combination of Currency Code and 'Value'. For example it might be just the concatenation of the values from those two fields with the formula:

           

          [font="courier"][Currency]+[/font][/quote]Or you might want to make a neater heading for output using an IF() function based formula but in effect achieving the same objective.

           

          This will give you a table something like:

           

           

          [font="courier"]B     1300     Value1       5.00    BValue1     

          B     1301     Value1      10.00    BValue1

          U     1300     Value1       7.20    UValue1

          U       1301     Value1       6.50    UValue1

          B       1300    Value2       6.00    BValue2

          B       1301    Value2      20.00    BValue2

          U       1300    Value2       1.23    UValue2

          U       1301    Value2       4.56    UValue2[/font][/quote]Now create a SUMMARY using the Reference Code as the first key and the new calculated field as the second key. FOr the Calculated Field Key entry right click to access the Advanced settings and set the DISPLAY to 'Across'.

           

          The numeric field (4th column above) will be your measure field.

           

          And that should be about it. Export from there.

           

          The order of display of the 'Across' field will be dicatated by the data string in the calculated field. If you need to ensure a certain order which is not a 'natural' sort order you can control that when you create the name if you use an IF() based formula. In V9 there may also be another way to do that but I would have to experiment to be certain.

           

          That solution hinges in the columns having accessible headings.

           

          If that is not the case then the 2 stage process using an initial extract and then lookup would be favourable. Only one model required and one filter if you have just the 2 currency codes to deal with.

           

          This might be easier to illustrate by developing a model for a sample of the real report(s). The description looks far more complicated than the process really is. Send me a PM if you think that would help.

           

          HTH.

           

          Grant

          • Lookup with a table
            fino _

            Thank you for a most comprehensive explanation - I shall attempt to follow it. I assumed that the fundamentals of the data import was the correct starting point

            cheers

            fino