topic Transpose data from Excel file in QlikView

Transpose data from Excel file

fredericvillemi — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

usually i'm working with databases but now I have to insert a big Excel file in a Qlikview project.

The columns are

idCustomer nameCustomer section1 section2 section3 section4

1 test 1 0 0 0

2 test2 0 2 0 0

And I would like to result to be

idCustomer nameCustomer sectionType value

1 test section1 1

2 test2 section2 2

Is there a built-in function to do that or should i do it myself with concatenation and big formulas ?

Thanks

Re: Transpose data from Excel file

avinashelite — Thu, 02 Jun 2016 09:42:13 GMT

try like this

Crosstable(sectionType,value,2)

LOAD idCustomer,

nameCustomer,

section1,

section2,

section3,

section4,

...

from excel

Re: Transpose data from Excel file

tamilarasu — Thu, 02 Jun 2016 09:45:00 GMT

Hi Frederic,

You can use cross table.

Data:
CrossTable(sectionType, Value, 2)
LOAD [idCustomer],
     [nameCustomer],
     [section1],
     [section2],
     [section3],
     section4
FROM
Book11.xlsx
(ooxml, embedded labels, table is Sheet1);

NoConcatenate
Final:
LOAD *
Resident Data where Value>0;

DROP Table Data;

Re: Transpose data from Excel file

MindaugasBacius — Thu, 02 Jun 2016 10:10:16 GMT

Use the table loading wizard to understand Crosstable function more precisely:

Re: Transpose data from Excel file

fredericvillemi — Thu, 02 Jun 2016 11:02:19 GMT

Thanks, so simple !