Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
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