topic Re: Case statement when loading from Excel in QlikView

Case statement when loading from Excel

Anonymous — Thu, 15 Aug 2013 14:34:52 GMT

Hi folks, been a while since using QV and need a refresher; I'm loading from Excel and need something similar to a CASE & LIKE statment and can't remember the exact syntax. I'm looking for something similar to below when loading from Excel (note Dept field is not present in the table I'm loading)

Dept =

CASE

WHEN user LIKE 'fi%' THEN 'Finance'

WHEN user LIKE 'sm%' THEN 'Sales&Mktg',

ELSE 'Other',

END,

Cheers

Re: Case statement when loading from Excel

Gysbert_Wassenaar — Thu, 15 Aug 2013 15:34:50 GMT

LOAD

...somefields...,

if(wildmatch(user, 'fi*'),'Finance', if(wildmatch(user, 'sm*'),'Sales&Mktg', 'Other')) as Dept

FROM ...somewhere...;

Re: Case statement when loading from Excel

swuehl — Thu, 15 Aug 2013 15:45:32 GMT

Or as an alternative, use a pick/match combination:

LOAD

....

pick(

wildmatch(user,'fi*','sm*',)+1

,'Other'

,'Finance'

,'Sales&Mktg'

) as Dept

FROM ....;

Might be easier to maintain if you add more cases.

Re: Case statement when loading from Excel

Sun, 01 Sep 2013 15:37:59 GMT

I like swuehl's solution best from a maintainability point of view, but if it's only going to be those three outcomes then the simpler solution might just be:

LOAD

....

,if(user like 'fi*' , 'Finance',

if(user like 'sm*', 'Sales&Mktg',

'Other') as Dept

FROM ....;