Apply Map function in qlik View/Sense

[cruzo008]

Hi All,

i want to a table with  "STATE/COUNTRY" wise "Product_Short_Name" wise monthly "Sales_Qty"  Vs.  "Forecst_Qty" .

to achieve this my data is as follows

Forecast
Month/DATE	Product code	CICS/Party CICS	Party_Code/PARTY_NO	Forecst_Qty.	STATE/COUNTRY	Product_Short_Name
01/06/2018 12:00:00 AM	179006	I0593E00	M0214	6	TELANGANA	MSP
01/07/2018 12:00:00 AM	179006	I1211T00	R0096	20	MAHARASHTRA	MDP
01/08/2018 12:00:00 AM	179006	I2047T00	L0043	15	KERELA	LG
01/04/2018 12:00:00 AM	179006	H4815C00	M0254	4	JHARKHAND	DMH

SALES
CICS/Party CICS	Month/DATE	Product/IM_CODE	Party_Code/PARTY_NO	Sales_Qty	STATE/COUNTRY
I0192C00	21/06/2018 12:00:00 AM	MSP00000003	M0214	5.4	TELANGANA
I0359Y00	03/07/2018 12:00:00 AM	MDP00001	R0096	9.9	MAHARASHTRA
I0359Y00	09/08/2018 12:00:00 AM	LG000004	L0043	9.9	KERELA
I0359Y00	14/04/2018 12:00:00 AM	DMH00001	M0254	9	JHARKHAND

MAP
Party_Code/PARTY_NO	CICS/Party CICS	Product/IM_CODE	Product code
M0214	I0192C00	MSP00000003	179006
R0096	I0359Y00	MDP00001	339000
L0043	I0359Y00	LG000004	370004
M0254	I0359Y00	DMH00001	368000

i dont know how to map them, can anyone help me on script part?

[vishsaggi]

May be try this? Rename the fields in Sales table accordingly. And i used Party_Code/PARTY_No as joining key.

Forecast:

LOAD [Month/DATE],

     [Product code],

     [CICS/Party CICS],

     [Party_Code/PARTY_NO],

     Forecst_Qty.,

     [STATE/COUNTRY],

     Product_Short_Name

FROM

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

(html, utf8, embedded labels, table is @1, filters(

Remove(Row, Pos(Top, 1))

));

Sales:

LOAD [CICS/Party CICS] AS SalesPartyCICS,

     [Month/DATE] AS SalesDate,

     [Product/IM_CODE] AS SalesProdIMCode,

     [Party_Code/PARTY_NO],

     Sales_Qty,

     F6 AS Sales_St_Country

FROM

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

(html, utf8, embedded labels, table is @3, filters(

Remove(Row, Pos(Top, 1))

));

[cruzo008]

May be I am confuse to get the output, but It would be great help if you could suggest me how to get this output –

month	Product_Short_Name	State	sales qty	forecast qty
Mar	MSP	TELANGANA	5.4	6
june	MDP	MAHARASHTRA	9.9	20
july	LG	KERELA	9.9	15
aug	DMH	JHARKHAND	9	4

Out of these three tables :-

Table1

Forecast
Month/DATE	Product code	CICS/Party CICS	Party_Code/PARTY_NO	Forecst_Qty.	STATE/COUNTRY	Product_Short_Name
01/06/2018 12:00:00 AM	179006	I0593E00	M0214	6	TELANGANA	MSP
01/07/2018 12:00:00 AM	179006	I1211T00	R0096	20	MAHARASHTRA	MDP
01/08/2018 12:00:00 AM	179006	I2047T00	L0043	15	KERELA	LG
01/04/2018 12:00:00 AM	179006	H4815C00	M0254	4	JHARKHAND	DMH

Table2

SALES
CICS/Party CICS	Month/DATE	Product/IM_CODE	Party_Code/PARTY_NO	Sales_Qty	STATE/COUNTRY
I0192C00	21/06/2018 12:00:00 AM	MSP00000003	M0214	5.4	TELANGANA
I0359Y00	03/07/2018 12:00:00 AM	MDP00001	R0096	9.9	MAHARASHTRA
I0359Y00	09/08/2018 12:00:00 AM	LG000004	L0043	9.9	KERELA
I0359Y00	14/04/2018 12:00:00 AM	DMH00001	M0254	9	JHARKHAND

Table3

MAP
Party_Code/PARTY_NO	CICS/Party CICS	Product/IM_CODE	Product code
M0214	I0192C00	MSP00000003	179006
R0096	I0359Y00	MDP00001	339000
L0043	I0359Y00	LG000004	370004
M0254	I0359Y00	DMH00001	368000