Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Crosstable question

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.

1 Solution

Accepted Solutions
alexandros17

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

View solution in original post

2 Replies
alexandros17

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

View solution in original post

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


talk is cheap, supply exceeds demand