topic Re: Crosstable ? in QlikView

Crosstable ?

firestream — Wed, 29 Jan 2014 16:20:02 GMT

Dear all,

I have a data set from Excel which column 2 is the comma delimiter data of profit center.

Column 1 , column 2

HK , V1,V2,V3,V4

US, P1,P2,P3,P4

How do I normalize the dataset so it read

HK V1

HK V2

HK V3

US P1 etc.

Using crosstable ? Tried the wizard but I can't get it .

Re: Crosstable ?

MK_QSL — Wed, 29 Jan 2014 16:31:22 GMT

May be like this?

Re: Crosstable ?

luis_pimentel — Wed, 29 Jan 2014 16:34:08 GMT

Asumming the excel is:

A B

HK	V1,V2,V3,V4
US	P1,P2,P3,P4

With a script like:

CrossTable(ProfitCenter,Value)

LOAD A as Column1,

SUBFIELD( B,',',1) as Column2,

SUBFIELD( B,',',2) as Column3,

SUBFIELD( B,',',3) as Column4,

SUBFIELD( B,',',4) as Column5

FROM

yourexcel.xlsx

(ooxml, no labels, table is Hoja1);

you will get a table like:

Column1	ProfitCenter	Value
HK	Column2	V1
HK	Column3	V2
HK	Column4	V3
HK	Column5	V4
US	Column2	P1
US	Column3	P2
US	Column4	P3
US	Column5	P4

Hope that helps.

Luis.

Re: Crosstable ?

maxgro — Wed, 29 Jan 2014 20:11:52 GMT

i think crosstable is not required for 2 columns excel like

Col1 Col2

HK	V1,V2,V3,V4
US	P1,P2,P3,P4

the script is

Directory;

Table:

LOAD Col1, SubField(Col2, ',')

FROM

[2colums.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Crosstable ?

rustyfishbones — Wed, 29 Jan 2014 20:17:05 GMT

You dont need crosstable, you need to use SUBFIELD ()

SUBFIELD (Column2,',') AS NewColumn2

Re: Crosstable ?

firestream — Wed, 05 Feb 2014 02:42:08 GMT

great . working well with the subfield . Thanks everyone

Re: Crosstable ?

MK_QSL — Wed, 05 Feb 2014 06:42:12 GMT

If you got answer, can you please mark Answered Question or Helpful Question to close this thread and other user can use also use it?

Thanks