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

    Lookup Formula

    Stefano Caminiti

      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,

         

      ControllerTypeCODELookup
      PIPPO2013PIPPO2013=vlookup(Type&(Code-1);A1:D16;3;false)
      PEPPE2013PEPPE2013
      PIPPO2014PIPPO2014
      PEPPE2014PEPPE2014
      PIPPO2015PIPPO2015
      PEPPE2015PEPPE2015
      CAIO2012CAIO2012
      PIPPO2012PIPPO2012
      PEPPE2016PEPPE2016
      CAIO2013CAIO2013
      PIPPO2016PIPPO2016
      PEPPE2012PEPPE2012
      CAIO2014CAIO2014
      CAIO2015CAIO2015
      CAIO2016CAIO2016

       

      thanks in advance for your help

       

      have a nice weekend

      SC

        • Re: Lookup Formula
          Gao Adam

          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

          • 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
                  Stefano Caminiti

                  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