topic Re: Case Statement in QlikView

Case Statement

Anonymous — Thu, 30 Jul 2015 11:48:25 GMT

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

Re: Case Statement

sorrakis01 — Thu, 30 Jul 2015 11:51:45 GMT

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

Re: Case Statement

PrashantSangle — Thu, 30 Jul 2015 11:53:58 GMT

Hi,

You can try with nested if -else.

Regards

Re: Case Statement

ogster1974 — Thu, 30 Jul 2015 12:22:39 GMT

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.

Re: Case Statement

Anonymous — Thu, 30 Jul 2015 12:26:27 GMT

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