topic how to map names to ID ? in App Development

how to map names to ID ?

gauravgg — Sun, 18 Dec 2016 06:08:51 GMT

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

Re: how to map names to ID ?

lironbaram — Sun, 18 Dec 2016 08:38:44 GMT

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 ?

vinieme12 — Sun, 18 Dec 2016 16:26:58 GMT

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