Transpose data from Excel file

[fredericvillemi]

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

[avinashelite · Accepted solution]

try like this

Crosstable(sectionType,value,2)

LOAD idCustomer,

          nameCustomer,

          section1,

          section2,

          section3,

          section4,

...

from excel

[tamilarasu]

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;

[MindaugasBacius]

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

[fredericvillemi]

Thanks, so simple !