Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
firestream
Contributor
Contributor

Crosstable ?

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 .

6 Replies
MK_QSL
MVP
MVP

May be like this?

luis_pimentel
Partner
Partner

Asumming the excel is:

A                       B

HKV1,V2,V3,V4
USP1,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:

Column1ProfitCenterValue
HKColumn2V1
HKColumn3V2
HKColumn4V3
HKColumn5V4
USColumn2P1
USColumn3P2
USColumn4P3
USColumn5P4

Hope that helps.

Luis.

maxgro
MVP
MVP

i think crosstable is not required for 2 columns excel like

Col1                       Col2

HKV1,V2,V3,V4
USP1,P2,P3,P4

the script is

Directory;

Table:

LOAD Col1, SubField(Col2, ',')

FROM

[2colums.xlsx]

(ooxml, embedded labels, table is Sheet1);

rustyfishbones
Master II
Master II

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

SUBFIELD (Column2,',') AS NewColumn2

firestream
Contributor
Contributor
Author

great . working well with the subfield . Thanks everyone

MK_QSL
MVP
MVP

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