how to map names to ID ?

[gauravgg]

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_ID	Customer Name	Type
001;002;003;004	ABC	A
003;002	PQR	B
001;	XYZ	B
004;001;002	LMN	D
001;003	SUV	A

Product table

Product_ID	Product_Name
001	Cloud Service
002	Internet Service
003	VM ware
004	Support

Final table output

Product_ID	Product_Name	Customer Name	Type
001;002;003;004	Cloud Service,Internet Srevice,VM ware,Support	ABC	A
003;002	VM ware,Internet Service	PQR	B
001;	Cloud Service	XYZ	B
004;001;002	Support,Cloud Service,Internet Service	LMN	D
001;003	Cloud Service,VM ware	SUV	A

can anyone help me in this .

Thanks in advance

[lironbaram]

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]

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);