topic Re: load excel with multiple lables as fields in QlikView

load excel with multiple lables as fields

Wed, 09 Apr 2014 18:12:02 GMT

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.

Re: load excel with multiple lables as fields

maxgro — Wed, 09 Apr 2014 19:00:55 GMT

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;

Re: load excel with multiple lables as fields

martinpohl — Wed, 09 Apr 2014 19:09:45 GMT

see attached as an example

Regards

Re: load excel with multiple lables as fields

Wed, 09 Apr 2014 19:44:47 GMT

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

Re: load excel with multiple lables as fields

maxgro — Wed, 09 Apr 2014 20:12:45 GMT

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.