Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

gauravgg
New Contributor III

how to map names to ID ?

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

Tags (2)
2 Replies
lironbaram
Honored Contributor II

Re: how to map names to ID ?

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;

vinieme12
Esteemed Contributor II

Re: how to map names to ID ?

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