topic Re: need help for Lookup function in QlikView

need help for Lookup function

tabisen1 — Sat, 04 Jul 2015 20:01:08 GMT

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.

Re: need help for Lookup function

Anonymous — Sat, 04 Jul 2015 21:29:16 GMT

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.

Re: need help for Lookup function

sinanozdemir — Sat, 04 Jul 2015 21:49:15 GMT

Hi Tarun,

You can also use Lookup function in the load script:

In a straight table, your result will look like:

Thanks

Re: need help for Lookup function

Anonymous — Sat, 04 Jul 2015 21:52:48 GMT

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

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

Regards.

Re: need help for Lookup function

tabisen1 — Sat, 04 Jul 2015 22:50:40 GMT

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.

Re: need help for Lookup function

tabisen1 — Sat, 04 Jul 2015 22:53:31 GMT

Dear Sinan,

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

Re: need help for Lookup function

tabisen1 — Sat, 04 Jul 2015 22:57:15 GMT

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

Re: need help for Lookup function

Anonymous — Sun, 05 Jul 2015 07:39:49 GMT

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.

Re: need help for Lookup function

Anonymous — Sun, 05 Jul 2015 07:57:35 GMT

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.

Re: need help for Lookup function

tabisen1 — Sun, 05 Jul 2015 17:11:20 GMT

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,

Re: need help for Lookup function

Anonymous — Sun, 05 Jul 2015 19:05:48 GMT

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.

Re: need help for Lookup function

tabisen1 — Mon, 06 Jul 2015 01:22:46 GMT

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,

Re: need help for Lookup function

Anonymous — Mon, 06 Jul 2015 08:08:10 GMT

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.

Re: need help for Lookup function

sinanozdemir — Mon, 06 Jul 2015 13:05:30 GMT

Yes, applymap is faster. Just tried to give the OP another solution.