Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
LOAD
...somefields...,
if(wildmatch(user, 'fi*'),'Finance', if(wildmatch(user, 'sm*'),'Sales&Mktg', 'Other')) as Dept
FROM ...somewhere...;
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.
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 ....;