Case statement in QlikView

[Anonymous]

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

[#2]

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

[Anonymous]

thank you Anosh

[#4]

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

[#5]

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

[#6]

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