topic Re: CASE function in QlikView

CASE function

Fri, 02 May 2014 10:55:49 GMT

Is there a CASE function, or something similar to it in Qlikview? I use the CASE function in Salesforce and it's useful

Definition: Checks a given expression against a series of values. If the expression is equal to a value, returns the corresponding result. If it is not equal to any values, it returns theelse_result
Use: CASE(expression,value1, result1, value2, result2,..., else_result) and replace expression with the field or value you want compared to each specified value. Replace each value and result with the value that must be equivalent to return the result entry. Replace else_result with the value you want returned when the expression does not equal any values.

Re: CASE function

anbu1984 — Fri, 02 May 2014 11:00:26 GMT

There is no case statement in Qlikview. You can mutiple Ifs

If(expression=value1, result1,If(expression=value2, result2,else_result))

Re: CASE function

swuehl — Fri, 02 May 2014 11:00:30 GMT

Use something like

pick(1+match(FIELD,'Value1','Value2')

, 'Default'

, 'Result1'

, 'Result2'

)

Re: CASE function

sushil353 — Fri, 02 May 2014 11:01:28 GMT

Hi,

You can use PICK() and MATCH() function

HTH

Sushil

Re: CASE function

Fri, 02 May 2014 11:30:16 GMT

I had thought about this, but wasn't sure if it was a bit messy.

Can you use it in conjunction with the GetFieldsSelections function? It seems to product some funny results when I use a nested if

Re: CASE function

anbu1984 — Fri, 02 May 2014 11:36:24 GMT

Yes.Can you post what you have tried?

Re: CASE function

Fri, 02 May 2014 11:44:35 GMT

=if(GetFieldSelections(Region)='EMEA'

,NUM(SUM({$<[Business area]={'EMEA broking'}>}[Global Revenue £(000)s]*1000),'£#,##0')

if(GetFieldSelections(Region)='APAC'

,NUM(SUM({$<[Business area]={'Asia pacific broking (excl china)'}>}[Global Revenue £(000)s]*1000),'£#,##0')

if(GetFieldSelections(Region)='Americas'

,NUM(SUM({$<[Business area]={'Americas broking'}>}[Global Revenue £(000)s]*1000),'£#,##0')

,NUM(SUM({$<[Business area]={'Total global broking trading'}>}[Global Revenue £(000)s]*1000),'£#,##0'))))

Re: CASE function

swuehl — Fri, 02 May 2014 11:58:44 GMT

Try something like

pick(1+match(GetFieldSelections(Region),'EMEA','APAC','Americas')

,NUM(SUM({$<[Business area]={'Total global broking trading'}>}[Global Revenue £(000)s]*1000),'£#,##0'))))

,NUM(SUM({$<[Business area]={'EMEA broking'}>}[Global Revenue £(000)s]*1000),'£#,##0')

,NUM(SUM({$<[Business area]={'Asia pacific broking (excl china)'}>}[Global Revenue £(000)s]*1000),'£#,##0')

,NUM(SUM({$<[Business area]={'Americas broking'}>}[Global Revenue £(000)s]*1000),'£#,##0')

)

This would only work properly with one selected Region.

Re: CASE function

simondachstr — Fri, 02 May 2014 12:16:17 GMT

You can also use the SubStringCount() function for a better overview and easy of implementation.

See my example here:

http://community.qlik.com/docs/DOC-5841

Re: CASE function

al_king — Fri, 18 Nov 2022 13:39:55 GMT

It's worth revisiting this:

Now that we have Coalesce(), we can combine it with the standard If() function for a very flexible Case()-equivalent.
The key insight here is that the 'else' clause in an If() is optional, and if it's absent it returns Null().

As such, we can go:

Coalesce(

If(condition1, result1),

If(condition2, result2),

If(condition3, result3),

default)