2 Replies Latest reply: Oct 16, 2017 2:46 AM by Joshua Goh RSS

    Qliksense consolidating 2 tables into one in data load editor

    Joshua Goh

      Hi!

       

      I'm using Qliksense and need some help with the following problem.

       

      I have 2 tables loaded via the data load editor. Let's call them T1 and T2.

       

      T1 & T2 contain the following columns:

      1. Product Code

      2. Product Name

       

      The Product Code is same for both tables but the Product Name may be slightly different across both tables (but refer to the same product). Some products may appear in T1 but not in T2 and vv.

       

      What I intend to do is create a T3 to consolidate both tables to have one product code be linked to a unique product name. So if a product code appears in both T1 and T2, I will select the Product Name in T2. Else if it only appears in one of the tables, I will use the available Product Name.

       

      Is this possible in the Data Load Editor? I have done it as a dimension in a table but would like to reuse it as across different visualizations so I thought it would be better to do it in the data load editor. Or is there a way to reuse a dimension from a table in the App sheet in another App sheet?

       

      Thanks!

        • Re: Qliksense consolidating 2 tables into one in data load editor
          omar bensalem

          It would be sthing like this:

           

          t1:

          load Distinct * , code&'-'&name as key;

          load * Inline [

          code, name

          1, a

          2,b

          3,c

          4,d

          ];

           

           

          t2:

          Load * where not Exists(code);

          load * where not Exists(key);

          load * , code&'-'&name as key;

          load * Inline [

          code, name

          1, aa

          2,b

          3,bc

          4,d

          5,e

          ];

           

           

          drop Field key from t1;

           

          result:

          Capture.PNG

           

           

           

          so per analogy to ur case:

           

          Table1:

          load Distinct * , "Product Code"&'-'& "Product Name" as key;

          load "Product Code",

                    "Product Name"

          from source1;


          Table2:

          Load * where not Exists("Product Code");

          load * where not Exists(key);

          load * , "Product Code"&'-'& "Product Name"as key;

          load "Product Code",

                    "Product Name"

          from source2;


          drop field key from Table1;

            • Re: Qliksense consolidating 2 tables into one in data load editor
              Joshua Goh

              Hi omarbensalem!

               

              Thanks for the help!

               

              There's one slight problem though. From what I tried, it seems the delta in the Product Codes are added but the Product Name for the corresponding delta Product codes are null.

               

              The result that i get from your example is as follows:

              1, a

              2,b

              3,c

              4,d

              5,null

               

              the code I'm using now is sth like:

               

              Table1:

              load Distinct * , "Product Code"&'-'& "Product Name" as key;

              load [number] as"Product Code",

                        [description] as "Product Name"

              from [lib://product1.xls] (biff,embedded labels, header is 2 lines, table is[product$];


              Table2:

              Load * where not Exists("Product Code");

              load * where not Exists(key);

              load * , "Product Code"&'-'& "Product Name"as key;

              load [f1] as"Product Code",

                        [f2] as"Product Name"

              from [lib://product2.xls] (biff,embedded labels, header is 2 lines, table is[product$];


              drop field key from Table1;