topic Crosstable question in QlikView

Crosstable question

Thu, 08 May 2014 16:11:24 GMT

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.

Re: Crosstable question

alexandros17 — Thu, 08 May 2014 16:19:45 GMT

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

Re: Crosstable question

Gysbert_Wassenaar — Thu, 08 May 2014 16:20:48 GMT

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

topic Crosstable question in QlikView

Crosstable question

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

FROM

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

Re: Crosstable question

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

FROM

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

Re: Crosstable question

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

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