topic Re: Case statement in QlikView in QlikView

Case statement in QlikView

Anonymous — Thu, 10 May 2012 10:20:31 GMT

Hi,

Could any one help me with converting SQL case statement in QlikView Code.

ProviderGroupName,

(Case

When A.Region in('L','4 - Lake','Lake and Polk County') Then 'Lake and Polk County'

When A.Region in('O','1-Orlando','Orlando') Then 'Orlando'

When A.Region in('T','2 - Tampa','Tampa') Then 'Tampa'

When A.Region in('3 - South Florida','South Florida') Then 'South Florida'

When A.Region in('J','5-JAX','Jacksonville') Then 'Jacksonville'

ELse isnull(A.Region,'') END) AS Region,

(Case

When A.Region in('L','4 - Lake','Lake and Polk County') Then 5

When A.Region in('O','1-Orlando','Orlando') Then 1

When A.Region in('T','2 - Tampa','Tampa') Then 2

When A.Region in('3 - South Florida','South Florida') Then 3

When A.Region in('J','5-JAX','Jacksonville') Then 4

ELse 6 END) AS DisplayOrderRow,

Thanks & Regards,

Shubham

Re: Case statement in QlikView

Thu, 10 May 2012 11:41:53 GMT

Hi Shubham,

Use mapping functions for doing this as:

Map1:
Mapping Load
* INLINE [
Key, Value
L, Lake and Polk County
4-Lake, Lake and Polk County
O, Orlando
1-Orlando, Orlando
T, Tampa
2 - Tampa, Tampa
    3 - South Florida, South Florida
    J, Jacksonville
    5-JAX, Jacksonville
];

Map2:
Mapping Load
* INLINE [
Key, Value
L, 5
4-Lake, 5
Lake and Polk County, 5
O, 1
1-Orlando, 1
Orlando, 1
T, 2
2 - Tampa, 2
Tampa, 2
    3 - South Florida, 3
    South Florida, 3
    J, 4
    5-JAX, 4
    Jacksonville, 4
];

load
ApplyMap('Map1',Region) as Region,
ApplyMap('Map2',Region) as DisplayOrderRow,

Hope this help,

Anosh

Case statement in QlikView

Anonymous — Fri, 11 May 2012 06:47:41 GMT

thank you Anosh

Re: Case statement in QlikView

Wed, 20 Jun 2012 13:05:04 GMT

Is it possible to use the mapping load to include a 'catch all' expression?

i.e

CASE [field]

WHEN aTHEN 1

WHEN b THEN 2

ELSE 99

Re: Case statement in QlikView

Wed, 20 Jun 2012 15:45:20 GMT

Hi Rich,

Yes it is possible. In applymap, the third parameter is used for the same.

ApplyMap('Map1',Region,'Third parameter') as Region,

Hope this help,

Anosh Nathaniel

Re: Case statement in QlikView

Wed, 20 Jun 2012 15:46:13 GMT

Thanks for you response../. I dont know how I missed that ! doh!!