1 Reply Latest reply: Oct 13, 2010 6:07 PM by ivan_cruz RSS

    How can I LOAD a row-based attribute database table into a standard column-based structure?

    stephen.tutino

      Hi all, MyTable structure in the database is as follows:
      RecordKey | FieldTag | FieldValue
      10 | Name | Frank
      10 | Age | 22
      10 | Sex | Male
      10 | Zip | 12345
      20 | Name | Mary
      20 | Age | 39
      20 | Sex | Female
      20 | Zip | 54321
      ...etc...

      I want to LOAD it into QlikView as a more standard columnar structure like this:
      ID | Name | Age | Sex | Zip
      10 | Frank | 22 | Male | 12345
      20 | Mary | 39 | Female | 54321
      ...etc...

      I suspect this is because the software vendor implemented a proprietary XML data acces layer for the dependent application. However, I want to generate some reports and it is easier to work with a standard columnar structure. What is the most efficient way to do this at LOAD time in QlikView 9? I can't seem to figure out the more advanced LOAD scripting functions. Thanks in advance!!

        • How can I LOAD a row-based attribute database table into a standard column-based structure?

          Hi stephen, it seems you have a cube-like structure there. I haven´t worked with "generic load" much, so I can't tell about performance. Generic load gives the effect you are looking for, however, it doesn't not build a single table as joining could do, it actually manages the relations through data model linking. The second option, as you might guessed, would be joining, I left the code of both approaches here, as well as an enclosed qvw, so you can see which one fits you better.

           

          //1st option Use generic load

          //Data:
          //generic load * inline [
          //RecordKey, FieldTag, FieldValue
          //10, Name, Frank
          //10, Age, 22
          //10, Sex, Male
          //10, Zip, 12345
          //20, Name, Mary
          //20, Age, 39
          //20, Sex, Female
          //20, Zip, 54321];

          //2nd option Use joining

          set v_attr_list = 'Name', 'Age', 'Sex', 'Zip';
          set v_attr_first = 'Name';

          Data:
          load * inline [
          RecordKey, FieldTag, FieldValue
          10, Name, Frank
          10, Age, 22
          10, Sex, Male
          10, Zip, 12345
          20, Name, Mary
          20, Age, 39
          20, Sex, Female
          20, Zip, 54321];

          for each attr in $(v_attr_list)

          if '$(attr)' = '$(v_attr_first)' then

          NewData:
          Load RecordKey,FieldValue as $(attr)
          resident Data
          where FieldTag = '$(attr)';

          else

          JoinData:
          join(NewData)
          Load RecordKey,FieldValue as $(attr)
          resident Data
          where FieldTag = '$(attr)';

          end if


          next

          drop table Data;

           

           

          If you choose the joining approach, perhaps the "keep" statement can be faster than a where clause, at least, it has happend to me, when working with large amount of data.

          Regards