Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

load lookup table for multiple columns

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

5 Replies

Re: load lookup table for multiple columns

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

Not applicable

Re: load lookup table for multiple columns

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
Contributor

Re: load lookup table for multiple columns

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
Contributor

Re: load lookup table for multiple columns

you probably will have to concatenate the lookupcode

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

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"

    ];

Not applicable

Re: load lookup table for multiple columns

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