load excel with multiple lables as fields

[#1]

hi all.

a co-worker presented an excel file that he wants to convert to qlikview.

here is and example of the file:

Project name	Management				Software
	Q1-2014	Q2-2014	Q3-2014	Q4-2014	Q1-2014	Q2-2014	Q3-2014	Q4-2014
Proj 1	20	30	15	45	30	35	55	18
Proj 2	35	11	55	23	23	77	32	12

i have tried different options with the transformation wizard, but no luck.

bottom line is that i need:

a field lable 'Management' that will show Q1-2014, Q2-2014..etc, and also as for the 'Software' lable.

a 'Project name' field that will show the project names.

and i want to be able to calculate the number.

attach is an excel sample.

can any one assist?

moti.

[maxgro]

Directory;

Table:

CrossTable(Quarter, Management)

LOAD F1,

     [Q1-2014],

     [Q2-2014],

     [Q3-2014],

     [Q4-2014]

FROM

[transform-test.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Tmp:

CrossTable(Quarter, Software)

LOAD F1,

     [Q1-20141] as [Q1-2014],

     [Q2-20141] as [Q2-2014],

     [Q3-20141] as [Q3-2014],

     [Q4-20141] as [Q4-2014]

FROM

[transform-test.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

join (Table) LOAD * Resident Tmp;

DROP Table Tmp;

[martinpohl]

see attached as an example

Regards

[#4]

thanks, Massimo.

very helpfull.

another q: how can i resolve/create a field that has a 'Department' lable with 'management', 'software'...etc in it?

Moti

[maxgro]

Please close this discussion by giving Correct and Helpful answers to the posts which are useful for you.  It helps others in finding answers for similar scenarios.