Crosstable ?

[firestream]

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 .

[MK_QSL]

May be like this?

[luis_pimentel]

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.

[maxgro]

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);

[rustyfishbones]

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

SUBFIELD (Column2,',') AS NewColumn2

[firestream]

great . working well with the subfield . Thanks everyone

[MK_QSL]

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