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?

[geert_gelade]

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);