1 Reply Latest reply: Feb 11, 2012 3:53 AM by Erica Whalley RSS

    Offset in QlikView

      Is there a function in QlikView that returns the reference to a range that is a given number of rows and columns away from a given reference, similar to the Offset function in Excel? 

       

      For instance, in my data set, I have a lookup column containing the exact names of other columns in the data set.  In one example, the lookup column contains the reference "Scsch14", which matches the name of a different column called "Scsch14".  I would like to create a new column in the Script that looks at the reference in the lookup column and returns the reference of the matched column with the same name. 

       

      Thank you!

        • Offset in QlikView

          Hi Bradensa

           

          There is no similar function in qlikview, as it is not based on a grid like excel, instead, like other databases each row is a separate entity with properties listed in the fields, so to speakl

           

          If you wish to use a column to look up the value of another column, you can use a "dollar sign expansion" to reference the column as script. If you are new to qlikview, I suggest you look these up in the reference document, as they are really useful.

           

          Basically, the dollar sign expression takes text in the brackets and evaluates it as if it was script.

           

          EG

          =$(='2+4') would evaluate to =6

          =$(='aFieldName') is equivalent to just typing in =aFieldName and displaying the result of that field.

           

          Where this can be used for your problem is to evaluate the the name of the column in the reference field as the actual name of the corresponding column.

           

          EG =$(=lookupcolumn) with the reference 'Scsch14' in that cell would be the same as writing =Scsch14 for that value.

           

          Let me know how you get on.

           

          Regards,

           

          Erica