Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I need help regarding the lookup function as their was many help regarding this.
but my query is different but regarding the lookup in single table.
I am trying to add column price 2 by surcharging from price 1 through factory_godown in godown to godown column and putting the price 1 value in the chart.
I am Attaching the base file which is created by qlik view.
Required table in qlik view chart
Cement_Flow | factory_godown | Godown to Godown | pics | AMOUNT | price1 | price 2 | price 1+2 |
F-G | 201L | 416L | 3,802 | 2,634,087.00 | 693 | 0 | 693 |
F-G | 202K | 407K | 6,525 | 1,827,000.00 | 280 | 0 | 280 |
F-G | 202K | 411K | 11,930 | 3,556,924.00 | 298 | 0 | 298 |
F-G | 202K | 473K | 3,938 | 3,084,132.00 | 783 | 0 | 783 |
F-G | 203M | 427M | 6,693 | 2,529,729.50 | 378 | 0 | 378 |
F-G | 209C | 473C | 3,822 | 5,633,896.00 | 1,474 | 0 | 1,474 |
F-G | 209C | 535C | 4,626 | 2,640,576.00 | 571 | 0 | 571 |
F-G | 209C | 537C | 7,667 | 4,938,180.00 | 644 | 0 | 644 |
F-G | 209C | 542C | 4,014 | 2,336,567.00 | 582 | 0 | 582 |
F-G | 219B | 427B | 7,770 | 10,525,860.00 | 1,355 | 0 | 1,355 |
F-G | 219B | 518B | 5,443 | 3,334,484.00 | 613 | 0 | 613 |
G-G | 407K | 401K | 245 | 200,165.00 | 817 | 280 | 1,097 |
G-G | 407K | 402K | 81 | 57,753.00 | 713 | 280 | 993 |
G-G | 411K | 419K | 25 | 12,500.00 | 500 | 298 | 798 |
G-G | 416L | 415L | 189 | 56,700.00 | 300 | 693 | 993 |
G-G | 427B | 825B | 258 | 63,210.00 | 245 | 1,355 | 1,600 |
G-G | 427M | 825M | 332 | 81,340.00 | 245 | 378 | 623 |
G-G | 473C | 410C | 800 | 320,000.00 | 400 | 1,474 | 1,874 |
G-G | 473L | 410L | 30 | 12,000.00 | 400 | 0 | 400 |
G-G | 518B | 517B | 17 | 1,819.00 | 107 | 613 | 720 |
G-G | 537C | 754C | 78 | 33,228.00 | 426 | 644 | 1,070 |
G-G | 542C | 419C | 326 | 91,280.00 | 280 | 582 | 862 |
Message was edited by: Tarun Bisen I have done in excel file but the data is very huge for that it is not possible to do all tome i have shared a sample of data. now i am attaching the final excel report file where i have done through using functions.
To undestand better I tried to elaborate another file (you can find Amount but you can calculate price1, price2 e nd price1+price2 if you want from the data).
Please can you control and say what is that dont work?
Thank you.
Best regards.
Hi,
try this, I do it with a Mapping load and ApplyMap in the script.
I calculated the price1+price2 with a preceding load of a preceding load but you can do this in the chart also.
Price_Map:
Mapping LOAD [Godown to Godown] AS factory_godown,
price1
FROM
[Base file vlookup fuction.xls]
(biff, embedded labels, table is Sheet2$);
Facts:
LOAD price1+price2 as [price1+price2],
*;
LOAD Cement_Flow,
factory_godown,
[Godown to Godown],
pics,
AMOUNT,
price1,
if(Cement_Flow='G-G',ApplyMap('Price_Map',factory_godown,0),0) as price2
FROM
[Base file vlookup fuction.xls]
(biff, embedded labels, table is Sheet2$);
I hope this can help you.
Best regards.
Hi Tarun,
You can also use Lookup function in the load script:
In a straight table, your result will look like:
Thanks
Yes, but I think the applymap is faster for performance.
This is a good question, I think, is mapping better for perfomance?
Regards.
Dear Giuseppe,
Thanks for the reply as that was the extracted file from qlik view.
I have try that with the original file it is not working properly. I am sending small data of the raw file.
Dear Sinan,
I have tried Lookup function but the same problem i am facing which i have face in applymap fuction.
I have to take out the per pice movement from factory to godown to godown amount per pice.
Dear Tarun,
I see your qvw with the Actual file vlookup function.
If I understand right you need the unit price and not the amount and amount2.
In you previuos final Excel report required you have the price1, price2 and price1+price2.
Or do you need the total amount, amount2 and amount+amount2 for each row?
Regards.
To undestand better I tried to elaborate another file (you can find Amount but you can calculate price1, price2 e nd price1+price2 if you want from the data).
Please can you control and say what is that dont work?
Thank you.
Best regards.
Dear Giupseppe,
I need the price which is the per pice (amount/pices) in the price2 if i
create the pivot table in the QV Price1 comes but the Price2 is not coming
as per the factory to godown and godown to godown for overview example
their is some movement like this:
from factory to godown the cost of transport is 1000 rs. and godown to
godown the cost of transport is 200 rs. then the total primary cost from
factory to customer will be 1200 rs.
In Price2 in second row their will be 746 rs which is not updating.
I need the help regarding this urgently as I am facing many problem.
Regards,