Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Say I have the following tables:
Table1:
Load * inline
[
ID,Table1_Code1,Table1_Code2
1,1,7
2,1,1
3,1,1
];
Table2:
Load * inline
[
ID,Table2_Code1,Table2_Code2
1,2,3
2,2,4
3,3,1
];
LookupTable:
Load * inline
[
ID,TableName,ColumnName,LookupCode,LookupValue
1,Table1,Table1_Code1,1,"Some value"
2,Table1,Table1_Code2,7,"seven"
3,Table1,Table1_Code2,1,"one"
4,Table2,Table2_Code1,1,"other value - one"
5,Table2,Table2_Code1,2,"other value - two"
6,Table2,Table2_Code1,3,"other value - three"
7,Table2,Table2_Code2,1,"yet another value - one"
8,Table2,Table2_Code2,2,"yet another value - two"
9,Table2,Table2_Code2,3,"yet another value - three"
10,Table2,Table2_Code2,3,"yet another value - four"
];
What would be the best way to get the LookupValue for each column (or a selection) joined?
I think some kind of method must exist, so I do not have to create manual load scripts per lookup column.
In my real data, I have more than 2000 different lookup columns, so I don't want to do that manually.
Best regards,
Koen
It's not clear for me what do you want to do - could you give a more practically example and some more informations where this should happens in script or gui or both of them, in a single application or a whole environment?
- Marcus
Well,
What I would like to have is for each column in all tables the corresponding lookup value.
For example, table1 should become something like (by Join or Mapping or...) so I can use the human readable values instead of the Codes to show up in the visualizations...
I would like to have this in my whole environment, since I have one Lookup table for 100 tables used throughout multiple applications.
Try this,
Table1:
Load * , Lookup('LookupValue', 'LookupCode', Table1_Code2_Value, 'LookupTable')
inline
[
ID,Table1_Code1, Table1_Code1_Value, Table1_Code2, Table1_Code2_Value
1,1,"Some Value",7,"seven"
2,1,"Some Value",1,"one"
3,1,"Some Value",1,"one"
];
you probably will have to concatenate the lookupcode
and then make a call
Table1:
Load * , Lookup('LookupValue', 'LookupCode', Table1_Code1&'-'&Table1_Code1_Value, 'LookupTable'),
Lookup('LookupValue', 'LookupCode', Table2_Code1&'-'&Table2_Code2_Value, 'LookupTable')
inline
[
ID,Table1_Code1, Table1_Code1_Value, Table1_Code2, Table1_Code2_Value
1,1,"Some Value",7,"seven"
2,1,"Some Value",1,"one"
3,1,"Some Value",1,"one"
];
One way would be to create mapping in a loop, something like
looklist:
LOAD
DISTINCT Col_type; // col_type is the identifier for each lookup
SQL SELECT * FROM Relation_lookup_table;
LET vLookCount = NoOfRows('LookList');
For i = 1 to vLookCount
LET vMap=FieldValue('Col_type',i);
'$(vMap)':
MAPPING
LOAD * ;
SQL SELECT * FROM Truven.IP_Relation
where Col_type = "'$(vMap)'" ;
NEXT i;
and then in the table each of the columns can be
applymap(vmap,lookup_val,default_value) as col_name