Lookup Formula

[#1]

Hi All

i have question, is possible to create a sort of lookup formula (like in excel) which look into the database and return a specified value. I guess is better to give an example that explain bettere then by word!!!

below an example of table and the formula that i would like to use,

Controller	Type	CODE	Lookup
PIPPO2013	PIPPO	2013	=vlookup(Type&(Code-1);A1:D16;3;false)
PEPPE2013	PEPPE	2013
PIPPO2014	PIPPO	2014
PEPPE2014	PEPPE	2014
PIPPO2015	PIPPO	2015
PEPPE2015	PEPPE	2015
CAIO2012	CAIO	2012
PIPPO2012	PIPPO	2012
PEPPE2016	PEPPE	2016
CAIO2013	CAIO	2013
PIPPO2016	PIPPO	2016
PEPPE2012	PEPPE	2012
CAIO2014	CAIO	2014
CAIO2015	CAIO	2015
CAIO2016	CAIO	2016

thanks in advance for your help

have a nice weekend

SC

[adamjiawen]

Hi Stefano,

if you want to do a vlookup in qlikview like what you have done in excel.

there're serveral ways, but they're all backend script ways.

you can have a try on join or mapping in qlikview.

regards.

Adam Gao

[pra_kale · Accepted solution]

Hi Stefano,

As per your requirement you can use Left Join, Inner join in your script. This joins works like a lookup in excel's.

In left join you will get all the matching values from second table as well as unmatched vales from Table one where as in inner join you will get only matched values of Table 1 and Table 2.

[marcus_sommer]

It will be depend which informations do you want to show the users what could be possible and in which it could be done. To think in excel-logics and to attempt to rebuild your excel 1:1 in qlikview will be rather frustrating - you should more think which informations you want to deliver and then you will find ways to do it in qlikview.

If I assume that your above example is quite near your real case you could sort to Type and Code and use then interecord-functions like above/below or before/after to catch the values from another rows/columns in your table. Another way might be to use several expressions with set analysis like:

sum({< Year = {"$(=max(Year) - 2)"}>} AnyValue)

sum({< Year = {"$(=max(Year) - 1)"}>} AnyValue)

sum({< Year = {"$(=max(Year))"}>} AnyValue)

In this way you could show the values direct side by side and if you combined them you could display their differences, like:

sum({< Year = {"$(=max(Year) - 2)"}>} AnyValue) / sum({< Year = {"$(=max(Year) - 1)"}>} AnyValue)

- Marcus

[#5]

Hi Marcus

thanks for your hint, what i would to achieve is something like you wrote

sum({< Year = {"$(=max(Year) - 2)"}>} AnyValue) / sum({< Year = {"$(=max(Year) - 1)"}>} AnyValue)

the problem is, as i shown in my first post, that the table has several years so i would show the incremental factor in comparison to the previous year in any cases and with your formula only the last 2 years will have values.

i guess that only the left join works for that purpose

ciao

SC