6 Replies Latest reply: Nov 15, 2016 2:04 PM by akshata tare RSS

    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

        • Re: load lookup table for multiple columns
          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

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

                • Re: load lookup table for multiple columns
                  sujith madhavan

                  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"

                      ];

                    • Re: load lookup table for multiple columns
                      sujith madhavan

                      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"

                          ];

                • Re: load lookup table for multiple columns
                  akshata tare

                  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