topic Mapping in QlikView

Mapping

emteam162 — Mon, 21 Mar 2016 10:10:03 GMT

Hi All,

Is that-is a way to do that let me explain :

i have this table

Purchase:

LOAD * Inline [

PurchaseID,Product,PurchasePrice,Quantity,PurchaseDate

P9, Product1, 8, 20, 15/10/2010

P11, Product1, 7, 10, 09/10/2010

P12, Product1, 9, 20, 01/10/2010

];

and i want apply function Mapping

Temp:

Mapping LOAD

Product,PurchasePrice

Resident Purchase;

DROP Table Purchase;

Sales:

LOAD ApplyMap('Temp', Product ) as PurchasePrice,* Inline [

SalesID,Product,SalesPrice,Quantity,SalesDate

S1, Product1, 10, 10, 10/10/2010

];

the final result I want is :

Product	PurchasePrice	Quantity	SalesDate	SalesID	SalesPrice
Product1	8	10	10/10/2010	S1	10
Product1	7	10	10/10/2010	S1	10
Product1	9	10	10/10/2010	S1	10

Product	PurchasePrice	PurchasePrice	PurchasePrice	Quantity	SalesDate	SalesID	SalesPrice
Product1	8	7	9	10	10/10/2010	S1	10

I attach an example.

Many thanks for your help

Said

Re: Mapping

sunny_talwar — Mon, 21 Mar 2016 10:15:17 GMT

I don't think mapping would work here because you have one product field mapped to three different purchase price. Mapping table can only be used when you have unique combinations. In your case, it would make sense to do a join.

Re: Mapping

MK_QSL — Mon, 21 Mar 2016 10:15:17 GMT

Purchase:

LOAD * Inline

[

PurchaseID, Product, PurchasePrice, Quantity, PurchaseDate

P9, Product1, 8, 20, 15/10/2010

P11, Product1, 7, 10, 09/10/2010

P12, Product1, 9, 20, 01/10/2010

];

Sales:

LOAD

Inline

[

SalesID, Product, SalesPrice, Quantity, SalesDate

S1, Product1, 10, 10, 10/10/2010

];

Left Join (Sales) Load Product, PurchasePrice Resident Purchase;

Drop Table Purchase;

Re: Mapping

avinashelite — Mon, 21 Mar 2016 10:16:16 GMT

mapping works on the unique key if you have duplicated keys only the first value will be considered in your case try with the Join it should work

try like this

Purchase:

LOAD * Inline [

PurchaseID,Product,PurchasePrice,Quantity,PurchaseDate

P9, Product1, 8, 20, 15/10/2010

P11, Product1, 7, 10, 09/10/2010

P12, Product1, 9, 20, 01/10/2010

];

and i want apply function Mapping

Sales:

LOAD

Product as PurchasePrice

,* Inline [

SalesID,Product,SalesPrice,Quantity,SalesDate

S1, Product1, 10, 10, 10/10/2010

];

Join(Sales)

LOAD

Product,

PurchasePrice

Resident Purchase;

DROP Table Purchase;

to avoid syndicate key rename the fields

Re: Mapping

emteam162 — Mon, 21 Mar 2016 10:22:50 GMT

Hi all,

I thank you for your reply I will use Left Join

its work

Many thanks for all

Best Regards,

Said

Re: Mapping

avinashelite — Mon, 21 Mar 2016 10:25:08 GMT

don't forget to mark the correct and helpful answers