1 Reply Latest reply: Sep 28, 2014 12:28 PM by Geert Geladé RSS

    Complex transpose rule (while importing from excel)

    Marko Rakar

      I have really interesting problem with excel load.

       

      There is an excel file with following collumn structure (each field contains some numerical value):

      • ID
      • category name1
      • item1
      • item2
      • item3
      • item4
      • item5
      • category name2
      • item1
      • item2
      • item3
      • item4
      • item5
      • category name3
      • item1
      • item2
      • item3
      • item4
      • item5

       

      What I want to do is to transpose this table while loading, but I also want to preserve structure and connections. This is not so easy because name of the category 1 is not contained in name of the item1 of that category. Also, sum of all items within category is not equal to the category name.

      What I always have is category name followed by fixed number of items within that category.

       

      What I want to achieve is to load all of this data into rows in a way so I can calculate sum of all numbers within category and indiividual items (see bolded section, this is correct sum for one category) - I also might want to do other calculations but this one is immediate problem.

       

      Any ideas on how to make script?

        • Re: Complex transpose rule (while importing from excel)
          Geert Geladé

          When you load the excel-file through the wizard, qlikview automatically assigns a unique number to the different columns. You can rename them if you want.

          Use Cross Table Load to transpose you data.

           

          CrossTable([category name1], Data)

          LOAD ID,

               [category name1],

               item1 as item1_1,

               item2 as item2_1,

               item3 as item3_1,

               item4 as item4_1,

               item5 as item5_1,

               [category name2],

               item11 as item1_2,

               item21 as item2_2,

               item31 as item3_2,

               item41 as item4_2,

               item51 as item5_2,

               [category name3],

               item12 as item1_3,

               item22 as item2_3,

               item32 as item3_3,

               item42 as item4_3,

               item52 as item5_3

          FROM

          datafile.xlsx

          (ooxml, embedded labels, table is Sheet1);