2 Replies Latest reply: Jan 3, 2018 2:45 AM by pradosh thakur RSS

    Splitting a data column into 2 columns during import

    josh goh

      Hi there!

       

      I would like to ask how I can approach the following:

       

      I have a dataset containing the following:

       

      Product Information

      ProductID | Price| Quantity | Expiry

      A001        | $50   | 10          | Dec-19

      A002        | $40   | 15          | Dec-19

      A003        | $30   | 15          | Dec-18

      B005        | $35   | 20          | Dec-18

      B006        | $45   | 20          | Dec-19


      Those productID starting with 'A' are old productIDs and 'B' are new productIDs.


      I have another dataset that contains the information to link the old and new ProductIDs.


      ProductID dataset

      ProductIDold | ProductIDnew

      A001             | B001

      A002             | B002

      A003             | B003


      ProductIDs that cannot be found in the table will be taken as is.


      When importing into Qliksense, I would like to seperate the new and the old productIDs to form the following table.

      ProductIDold |ProductIDnew | Price| Quantity | Expiry

      A001             | B001             | $50   | 10          | Dec-19

      A002             | B002             | $40   | 15          | Dec-19

      A003             | B003             | $30   | 15          | Dec-18

      -                    | B005             | $35   | 20          | Dec-18

      -                    | B006             | $45   | 20          | Dec-19


      So to summarize what needs to be performed:

      1) Seperate the ProductID column to ProductIDold and ProductIDnew columns when importing. The other 3 columns will be imported accordingly(Price,Quantity,Expiry).

      2) Add the ProductIDnew if there is a ProductIDold using the ProductID dataset


      I've tried the following for part 1) but didn't seem to work.

      load

           if(wildmatch(productID,'A'),productID) as ProductIDold,

           if(wildmatch(productID,'B'),productID) as ProductIDnew,

           Price,

           Quantity,

           Expiry

       

        Thanks in advance!

       


        • Re: Splitting a data column into 2 columns during import
          Mohammed Mukram Ali

          Hi,

           

          Can you try using ApplyMap:

           

          Product_Map:

          Mapping LOAD * Inline

          [

          ProductIDold,ProductIDnew

          A001,B001

          A002,B002

          A003,B003

          ]

          ;

          Product_Information:

          LOAD *,

          if(WildMatch(ProductID,'A*'),ProductID) as ProductIDold,

          ApplyMap('Product_Map',ProductID) as ProductIDnew

          Inline[

          ProductID,Price, Quantity,Expiry

          A001,$50  ,10  ,Dec-19

          A002,$40  ,15  ,Dec-19

          A003,$30  ,15  ,Dec-18

          B005,$35  ,20  ,Dec-18

          B006,$45  ,20  ,Dec-19

          ]

          ;

          DROP Field ProductID;


          1410208.PNG

          • Re: Splitting a data column into 2 columns during import
            pradosh thakur

            map1:

            mapping

            load ProductIDold as ProductID,ProductIDnew;

            load * inline [

            ProductIDold | ProductIDnew

            A001             | B001

            A002             | B002

            A003             | B003

            ] (delimiter is |);

             

             

            load *,ApplyMap('map1',ProductID,ProductID) as ProductIDnew,PICK(WildMatch(ProductID,'A*'),ProductID) AS ProductIDold;

             

             

            load * inline [

            ProductID | Price| Quantity | Expiry

            A001        | $50   | 10          | Dec-19

            A002        | $40   | 15          | Dec-19

            A003        | $30   | 15          | Dec-18

            B005        | $35   | 20          | Dec-18

            B006        | $45   | 20          | Dec-19

            ] (delimiter is |);

             

             

            DROP FIELD ProductID;


            Capture.PNG