matching existing data with new data

[thanhphongle]

Hello  community,

I have an Excel Data with two sheets (Sheet1 = Customer1, Sheet2=Customer2) which has completele the same structure but with some different datas.

Example:

First Sheet

Customer 1

Customername	Product	Cost
Customer1	A	10 €
Customer1	B	20 €
Customer1	C	30 €
Customer1	D	40 €

Second Sheet

Customer 2

Customername	Product	Cost
Customer2	A	Unknown
Customer2	B	20 €
Customer2	C	30 €
Customer2	D	Unknown
Custmoer2	E	50 €
Customer2	F	60 €

As you can see the products are the same but some costs are still unknown for product A and D from Customer 2 although the cost are the same as like customer 1.

What I like to do is, that qv should adapt the existing costs if the name of the product is the same. Something like this

If  Product A = Product A from existing Data, then give out Cost of Product A = 10 €

so the new table should be like this:

Customername	Product	Cost
Customer2	A	10 €
Customer2	B	20 €
Customer2	C	30 €
Customer2	D	40 €

and with the adapting result i would like to calculate afterwards. something like

If  Product A = Product A from existing Data, then give out Cost of Product A = 10 € ---> Avg(Cost) from customer2

is this prossible in QV?

[stigchel]

Yes it is possible, you can use the lookup function, something like

Data:

LOAD Customername,

    Product,

    Cost

FROM

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

(html, codepage is 1252, embedded labels, table is @1);

Concatenate

LOAD Customername,

    Product,

    if(Cost='Unknown',Lookup('Cost','Product',Product,'Data'),Cost) as Cost

FROM

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

(html, codepage is 1252, embedded labels, table is @2);

//lookup('Price', 'ProductID', InvoicedProd, 'pricelist')

See also attached

[maxgro]

FirstSheet:

LOAD Customername,

     Product,

     Cost

FROM

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

(html, codepage is 1252, embedded labels, table is @1);

PriceMap:

Mapping LOAD Product, Cost

Resident FirstSheet;

Concatenate (FirstSheet)

LOAD Customername,

     Product,

     if(Cost ='Unknown', ApplyMap('PriceMap', Product), Cost) as Cost

FROM

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

(html, codepage is 1252, embedded labels, table is @2);