adding a column during LOAD in CROSSTABLE

[#1]

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

[#2 · Accepted solution]

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

[#3]

Thanks, Rainer!

Runs smoothly