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
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
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;
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:
This will give you a table something like:
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.