Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
cruzo008
New Contributor II

Apply Map function in qlik View/Sense

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/DATEProduct codeCICS/Party CICSParty_Code/PARTY_NOForecst_Qty.STATE/COUNTRYProduct_Short_Name
01/06/2018 12:00:00 AM179006I0593E00M02146TELANGANAMSP
01/07/2018 12:00:00 AM179006I1211T00R009620MAHARASHTRAMDP
01/08/2018 12:00:00 AM179006I2047T00L004315KERELALG
01/04/2018 12:00:00 AM179006H4815C00M02544JHARKHAND

DMH

   

SALES
CICS/Party CICSMonth/DATEProduct/IM_CODEParty_Code/PARTY_NOSales_Qty
STATE/COUNTRY
I0192C0021/06/2018 12:00:00 AMMSP00000003M02145.4TELANGANA
I0359Y0003/07/2018 12:00:00 AMMDP00001R00969.9MAHARASHTRA
I0359Y0009/08/2018 12:00:00 AMLG000004L00439.9KERELA
I0359Y0014/04/2018 12:00:00 AMDMH00001M02549JHARKHAND

 

MAP
Party_Code/PARTY_NOCICS/Party CICSProduct/IM_CODEProduct code
M0214I0192C00MSP00000003179006
R0096I0359Y00MDP00001339000
L0043I0359Y00LG000004370004
M0254I0359Y00DMH00001368000

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

2 Replies
vishsaggi
Esteemed Contributor III

Re: Apply Map function in qlik View/Sense

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
New Contributor II

Re: Apply Map function in qlik View/Sense

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

  1. 5.4

6

june

MDP

MAHARASHTRA

  1. 9.9

20

july

LG

KERELA

  1. 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

  1. 5.4

TELANGANA

I0359Y00

03/07/2018 12:00:00 AM

MDP00001

R0096

  1. 9.9

MAHARASHTRA

I0359Y00

09/08/2018 12:00:00 AM

LG000004

L0043

  1. 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