Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.
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
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