4 Replies Latest reply: Feb 29, 2016 8:36 AM by Stefano Caminiti RSS

    Lookup Formula

      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,




      thanks in advance for your help


      have a nice weekend


        • Re: Lookup Formula
          AAA AAA

          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.



          Adam Gao

          • Re: Lookup Formula
            Prasad Kale

            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.

            • Re: Lookup Formula
              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

                • Re: Lookup Formula

                  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