SQL Query to Qlikview Script

Tue, 15 Oct 2013 13:17:45 GMT

Hi all,

I need to change SQL CASE statements to Qlikview script. I am not sure how to do it.

Here are case statements:

CASE 1:

SELECT Loc,

CASE [Loc]
WHEN '123 East' THEN ‘East’
WHEN '123 West' THEN ‘West’

        WHEN '123 North' THEN ‘North’
        WHEN '123 South' THEN ‘South’
        ELSE [Loc]
      END AS Location

     ,     CASE [Loc]
        WHEN '123 East' THEN ‘777 East location’
        WHEN '123 West' THEN ‘777 West location’

        WHEN '123 North' THEN ‘777 West location’
        WHEN '123 South' THEN ‘777 West location’
        ELSE [Loc]
      END AS LocationFlag

FROM TABLE_A
where [Loc] in ('123 East'

,'123 West'

,'123 North'

,'123 South');

CASE 2:

SELECT Dep,

CASE [Dep]
WHEN '123 HR_A' THEN ‘HR’
WHEN '123 HR_B' THEN ‘HR’

        WHEN '123 HR_c' THEN ‘HR’
        WHEN '123 HR_D' THEN ‘HR’
        ELSE [Dep]
      END AS Location

, ‘777 HR’ as LocationFlag

FROM TABLE_A
where [Dep] in ('123 HR_A'

,'123 HR_B'

,'123 HR_C'

,'123 HR_D');

Can someone help me out please ?

Thanks

Re: SQL Query to Qlikview Script

swuehl — Tue, 15 Oct 2013 13:44:27 GMT

You can look into a pick(match()) function combination:

pick(match( [Loc] , '123 East' ,'123 West' ,'123 North','123 South')+1, //add 1 for default

[Loc], //default

‘East’,

‘West’,

‘North’ ,

‘South’

) as Location