Crosstable question

[#1]

Hi,

I have the following code and loading in some data into a cross table as shown below:

DIRECTORY;
Tester_File:
CrossTable ([Business Units], Data, 30)
LOAD *

FROM

(ooxml, embedded labels, header is 3 lines, table is [Master Application List]);

So I am loading the [Business Units] into the Data field and transposing this. However I then need this transposed Data field to then be  split in two separate chunks.

So eg. the Business Unit is "England-London" and I need to split is in England and London in two separate fields.

I am missing something to complete my coding to be in the likes of:

Load

     left([Business Units],Index([Business Units], '-')-1)) as Country,

     right([Business Units],Index([Business Units], '-'))) as City

From

Data   

Hope this makes sense!

Thank you for your help.

[alexandros17 · Accepted solution]

Ciao Roberto,

do per assunto che la crosstable funzioni, per cui prova il seguente script:

Tester_File:
CrossTable ([Business Units], Data, 30)
LOAD *

FROM

(ooxml, embedded labels, header is 3 lines, table is [Master Application List]);

Final_Tester:

noconcatenate

Load

     *,

     Left([Business Units],index([Business Units],'-')) as Country,

     Mid([Business Units],index([Business Units],'-')+1) as City,

Resident Tester_File;

Drop table Tester_File;

Fammi sapere

[Gysbert_Wassenaar]

First do the crosstable stuff, then use that table to create a new one and split the Business Units field in the second load that creates that new table. Something like this:

Temp:

CrossTable(...etc

Load ... etc

Result:

LOAD *,

subfield([Business Units],'-',1) as Country,

subfield([Business Units],'-',2) as City

Resident Temp;

Drop Table Temp;

//optionally

//Drop field [Business Units];