Case Statement

[Anonymous]

Hi guys,

I have a case statement

case

  when (territory = 'EXP FR' or territory like 'FRA%') then 'France'

  when territory = 'EXP IR' then 'Ireland'

  when territory in ('NIR WH','NWA WH','SCO WH','NOR WE','SWE WH','NOR EA','EMD WH','MAN WE','MAN EA','MID WH',

  'SEA SO','SEA WH','SWA WH','EAN WH','SCO WH') then 'UK'

  when territory = 'EXP WH' then 'ROW'

  when territory = 'EXP BE' then 'Belgium'

  when territory = 'EXP GE' then 'Germany'

  when territory = 'EXP CH' then 'Channel Islands' end as customer_country

I know this is a mixture of wildmatch and match, so is it possible to put this into a load script or is this best kept in a SQL statement?

Thanks

[sorrakis01]

Hi,

Case statement is not possible in LOAD script

Use ifs and Match function.

a little example:

LOAD

  If(territory = 'EXP FR','France',

  If(territory = 'EXP IR', 'Ireland',

  If(Match(territory,'NIR WH','NWA WH','SCO WH','NOR WE','SWE WH','NOR EA','EMD WH','MAN WE','MAN EA','MID WH','SEA SO','SEA WH','SWA WH','EAN WH','SCO WH')>0,'UK','ROW'))) AS territory;

LOAD * INLINE [

    territory

    EXP FR

    EXP IR

    NIR WH

];

Regards

[PrashantSangle]

Hi,

You can try with nested if -else.

Regards

[ogster1974]

Perform the case statement in an SQL View and bring that across.

Or as you suggest use Pick and Wildmatch in the load.

Pick(WildMatch(territory, 'EXP FR', 'EXP IR', '*'),

     'France',

     'Ireland',

     'Other') AS  customer_country

Compare times to load the view verse doing it in script and take the quicker option.  Both will work.

[Anonymous]

Cheers guys! I will just keep it in the SQL then I think..