Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

tabisen1
New Contributor

need help for Lookup function

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_Flowfactory_godownGodown to GodownpicsAMOUNTprice1price 2price 1+2
F-G201L416L3,8022,634,087.006930693
F-G202K407K6,5251,827,000.002800280
F-G202K411K11,9303,556,924.002980298
F-G202K473K3,9383,084,132.007830783
F-G203M427M6,6932,529,729.503780378
F-G209C473C3,8225,633,896.001,47401,474
F-G209C535C4,6262,640,576.005710571
F-G209C537C7,6674,938,180.006440644
F-G209C542C4,0142,336,567.005820582
F-G219B427B7,77010,525,860.001,35501,355
F-G219B518B5,4433,334,484.006130613
G-G407K401K245200,165.008172801,097
G-G407K402K8157,753.00713280993
G-G411K419K2512,500.00500298798
G-G416L415L18956,700.00300693993
G-G427B825B25863,210.002451,3551,600
G-G427M825M33281,340.00245378623
G-G473C410C800320,000.004001,4741,874
G-G473L410L3012,000.004000400
G-G518B517B171,819.00107613720
G-G537C754C7833,228.004266441,070
G-G542C419C32691,280.00280582862

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.

1 Solution

Accepted Solutions
ggallina
Contributor III

Re: need help for Lookup function

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.

13 Replies
ggallina
Contributor III

Re: need help for Lookup function

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.

sinanozdemir
Valued Contributor III

Re: need help for Lookup function

Hi Tarun,

You can also use Lookup function in the load script:

Capture.PNG

In a straight table, your result will look like:

Capture.PNG

Thanks

ggallina
Contributor III

Re: need help for Lookup function

Yes, but I think the applymap is faster for performance.

This is a good question, I think, is mapping better for perfomance?

Regards.

tabisen1
New Contributor

Re: need help for Lookup function

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.

tabisen1
New Contributor

Re: need help for Lookup function

Dear Sinan,

I have tried Lookup function but the same problem i am facing which i have face in applymap  fuction.

tabisen1
New Contributor

Re: need help for Lookup function

I have to take out the per pice movement from factory to godown to godown amount per pice.

ggallina
Contributor III

Re: need help for Lookup function

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.

ggallina
Contributor III

Re: need help for Lookup function

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.

tabisen1
New Contributor

Re: need help for Lookup function

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,

Community Browser