Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

adding a column during LOAD in CROSSTABLE

Hi all,

I don't know if this is possible at all... however unfortunately my script doesn't do the trick.

While LOADing a CROSSTABLE I would like to add a column which contains values based on a CASE check. My script looks like the following:

Table1:

CROSSTABLE (A, B, 11) LOAD year,
[Emp.-No.],
CASE WHEN "Emp.-No."<1000
then 'US'
CASE WHEN "Emp.-No.">=1000 AND <2000
then 'CA'
ELSE 'MX'
END as "Group",
Office,
*
FROM...

Am I following the wrong approach to this or is this an abuse of the syntax?? [6]

Any help is highly appreciated!! Thanks in advnace

Torben

1 Solution

Accepted Solutions
Highlighted
Not applicable

adding a column during LOAD in CROSSTABLE

Hi Torben,

not exactly sure but you can try this statement. If you are adding a additional field in your crosstable load you have to change the 11 into 12 (stands for: how many regular fields in your original table).

CROSSTABLE (A, B, 12) LOAD year,
[Emp.-No.],
If( "Emp.-No."<1000, 'US',
If( "Emp.-No.">=1000 AND "Emp.-No." <2000, 'CA', 'MX')) as "Group",
Office,
*
FROM...

Otherwise do a resident load of your crosstable and put in the required if statement here.

Good luck!

Rainer

View solution in original post

2 Replies
Highlighted
Not applicable

adding a column during LOAD in CROSSTABLE

Hi Torben,

not exactly sure but you can try this statement. If you are adding a additional field in your crosstable load you have to change the 11 into 12 (stands for: how many regular fields in your original table).

CROSSTABLE (A, B, 12) LOAD year,
[Emp.-No.],
If( "Emp.-No."<1000, 'US',
If( "Emp.-No.">=1000 AND "Emp.-No." <2000, 'CA', 'MX')) as "Group",
Office,
*
FROM...

Otherwise do a resident load of your crosstable and put in the required if statement here.

Good luck!

Rainer

View solution in original post

Highlighted
Not applicable

adding a column during LOAD in CROSSTABLE

Thanks, Rainer!

Runs smoothly Yes Yes