2 Replies Latest reply: Dec 18, 2016 11:26 AM by Vineeth Pujari RSS

    how to map names to ID ?

    gaurav gopale

      Hi

      I have the following two tables "Customer" and "Product"

      I want product bring product name in customer table separated by comma .

      The output should be the Final output table.

       

      Customer Table

      Product_IDCustomer NameType
      001;002;003;004ABCA
      003;002PQRB

      001;

      XYZB
      004;001;002LMND
      001;003SUVA

       

      Product table

      Product_IDProduct_Name
      001Cloud Service
      002Internet Service
      003VM ware
      004Support

       

      Final table output

      Product_IDProduct_NameCustomer NameType
      001;002;003;004Cloud Service,Internet Srevice,VM ware,SupportABCA
      003;002VM ware,Internet ServicePQRB

      001;

      Cloud ServiceXYZB
      004;001;002Support,Cloud Service,Internet ServiceLMND
      001;003Cloud Service,VM wareSUVA

       

      can anyone help me in this .

       

      Thanks in advance

        • Re: how to map names to ID ?
          Liron Baram

          hi

          this script does what you look for,

          also attach is a demo app

           

          //////////////load products discription as map//////////////

          productmap:

          Mapping LOAD

              Product_ID,

              Product_Name

          FROM [lib://Source/sample.xlsx]

          (ooxml, embedded labels, table is Sheet2);

           

           

          ///////////////saperate the product id field  and add the product name/////////////

          DataTemp:

          load *,

               ApplyMap('productmap',Product_ID) As Product_Name;

          LOAD

              SubField(Product_ID,';') As Product_ID,

              "Customer Name",

              "Type"

          FROM [lib://Source/sample.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

           

          //////////////////////////////group the records back to the intial state////////////

          Data:

          NoConcatenate load  "Customer Name",

               Type,

               Concat(Product_ID,';') as Product_ID,

                Concat(Product_Name,';') as Product_Name

          Resident DataTemp

          group by "Customer Name",Type;

           

           

           

           

          drop Table DataTemp;

          • Re: how to map names to ID ?
            Vineeth Pujari

            Even simpler to make use of a native function, MapSubString()

             

            Product:

            Mapping LOAD Product_ID,

                 Product_Name

            FROM

            [https://community.qlik.com/thread/243696]

            (html, codepage is 1252, embedded labels, table is @2);

             

             

            FACT:

            LOAD Product_ID,

              MapSubString('Product',Product_ID) as Prod_NAME,

                 [Customer Name],

                 Type

            FROM

            [https://community.qlik.com/thread/243696]

            (html, codepage is 1252, embedded labels, table is @1);

             

            243696.JPG