Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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);