1 Reply Latest reply: Apr 27, 2011 10:44 AM by Richard Pearce RSS

    Transforming data from a table where the field names need to become values

      Hi, sorry if I have missed a posted answer to this.....

      I need to change the original data I have into something I can use in my QlikView dashboard. Its a simple report and I have got it to work although the method I used is ropy at best and if the row numbers increase in a future project (which I know they will) I think I'll run into speed issues (plus I prefer to do things right if possible)

      The original table is:

      Name - Text

      Report Name 1 - 1 or Null

      Report Name 2 - 1 or Null

      Report Name 3 - 1 or Null

      Etc .......


      What I need to do (I think) is create a new table with only: Name and Report as the column headers where Report field in each record holds the Column name of the original table (if the value was = 1) . for example:

      Name Report

      Richard Report Name 1

      Richard Report Name 2

      John Report Name 2

      John Report Name 3



      This is the simple code I am using which does the job but I'm not sure is the correct way to tackle this problem..

      load Name,
      if(Operational='1','Operational') as Report
      Resident Temp_Data;

      Concatenate LOAD Name,
      if(Divisional='1','Divisional') as Report
      Resident Temp_Data;

      Concatenate LOAD Name,
      if([Forecast + Capacity]='1','Forecast + Capacity') as Report
      Resident Temp_Data;


      Two things I guess I would like to know; what's the most efficient way to transform the data and how could I make it more intelligent on the off change the original table expands to include more report names (ie For Loop,)?


      Sorry for the long post - thanks in advance (using QV10)