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.
Dear Tarun,
in you example you have select only rows that have Goods_Flow='F-G' and in the script the price2 is calculate only for Goods_Flow='G-G' (script line: if(Goods_Flow='G-G',ApplyMap('Price_Map',factory_godown,0),0) as Price2).
So i normal that you dont have the price2.
Maybe you should delete the if statementin ten script and replace it with ApplyMap('Price_Map',factory_godown,0) as Price2?
Regards.
Dear Giuseppe,
I am very sorry that i have done the wrong selection yesterday i am sending
the snap short of the selection the price2 is coming 285 insect of 298.
In below snap short you can see the flow of goods from factory to the final
godown.( 798)
Regards,
Dear Tarun,
The price2 in the line factory godown 411K and goodown to godown 419K have price2 285 because if you cntrol the Excel file Actual file vlookup fuction.xls Sheet1 (the script read from this sheet and not from sheet2) the price of G-G 411K (Amount/pics that is loaded with Mapping Load and then with Applymap) is 285. So it is correct. The price2 298 is in the sheet2, you can load this in the mapping load if you want.
Regards.
Yes, applymap is faster. Just tried to give the OP another solution.