Complex transpose rule (while importing from excel)

marko_rakar — Sat, 27 Sep 2014 10:41:44 GMT

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_gelade — Sun, 28 Sep 2014 16:28:13 GMT

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

topic Re: Complex transpose rule (while importing from excel) in QlikView

Complex transpose rule (while importing from excel)

Re: Complex transpose rule (while importing from excel)