load lookup table for multiple columns

[#1]

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

[marcus_sommer]

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

[#3]

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...

1. Table1:
2. Load * inline
3. [
4. ID,Table1_Code1, Table1_Code1_Value, Table1_Code2, Table1_Code2_Value
5. 1,1,"Some Value",7,"seven"
6. 2,1,"Some Value",1,"one"
7. 3,1,"Some Value",1,"one"
8. ];

I would like to have this in my whole environment, since I have one Lookup table for 100 tables used throughout multiple applications.

[sujith1011]

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"

    ];

[sujith1011]

you probably will have to concatenate the lookupcode

1. LookupTable: 
2. Load * inline 
3. [ 
4. ID,LookupCode,LookupValue 
5. 1,Table1-Table1_Code1-1,"Some value" 
6. 2,Table1-Table1_Code2-7,"seven" 
7. 3,Table1-Table1_Code2-1,"one" 
8. 4,Table2-Table2_Code1-1,"other value - one" 
9. 5,Table2-Table2_Code1-2,"other value - two" 
10. 6,Table2-Table2_Code1-3,"other value - three" 
11. 7,Table2-Table2_Code2-1,"yet another value - one" 
12. 8,Table2-Table2_Code2-2,"yet another value - two" 
13. 9,Table2-Table2_Code2-3,"yet another value - three" 
14. 10,Table2-Table2_Code2-3,"yet another value - four" 
15. ]; 

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"

    ];

[#6]

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