Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Not applicable
Author

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
Not applicable
Author

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

Not applicable
Author

Thanks, Rainer!

Runs smoothly Yes Yes