topic Re: CASE statement in QlikView in QlikView

CASE statement in QlikView

Thu, 17 May 2012 12:45:43 GMT

Hi,

I was wondering if anyone would be able to help me. I want to create the following Case statement in QlikView:

CASE WHEN [Account Name] IN ('Hong Kong') THEN 'HK'

WHEN [Account Name] IN ('USA') THEN 'NY'

WHEN [Account Name] IN ('UK') THEN 'LDN'

ELSE 'OTHER'

END

Is this possible to do within QlikView?

Any help would be greatly appreciated.

Many thanks.

Rajiv.

Re: CASE statement in QlikView

Thu, 17 May 2012 12:54:12 GMT

if(wildmatch([Account Name],'*Hong Kong*', 'HK',

if(wildmatch([Account Name],'*USA*', 'NY',

if(wildmatch([Account Name],'*UK*','LDN',

'OTHER')))

Wildmatch works if you need any string with the resulting '*string*' in it. If you are looking for exact match then just match(field,'String') will work too

CASE statement in QlikView

Thu, 17 May 2012 13:11:09 GMT

That worked! Thank you!

Re: CASE statement in QlikView

Tue, 17 Dec 2013 15:05:47 GMT

Mark,can u please help me with the below case statement .

CASE WHEN ITEM_ID LIKE '%CB%' THEN SUBSTR(ACCOUNT_NO,4,10)

WHEN ITEM_ID LIKE 'D%' THEN SUBSTR(ACCOUNT_NO,5,8)

ELSE ACCOUNT_NO END AS InSite_Acct,

I am trying like this below.It is giving me error.Can anybody tell me what am I doing wrong here?

if(WildMatch([ITEM ID],'*CB*',mid([Account Number],4,10)

if(WildMatch([ITEM ID],'*D*',mid([Account Number],5,8),([Account Number])) as INsite_Acct,

Re: CASE statement in QlikView

VishalWaghole — Tue, 17 Dec 2013 18:20:00 GMT

Hi,

Please try this one hope that will work,

=if(WildMatch([ITEM ID],'*CB*',mid([Account Number],4,10),

if(WildMatch([ITEM ID],'*D*',mid([Account Number],5,8),([Account Number])))))

- Regards,

Vishal Waghole

Re: CASE statement in QlikView

Fri, 11 Apr 2014 11:03:37 GMT

case

when saleschannel='Online' and (userid is null or userid not like '%RB:MOBILE%') then 'ONLINE WEB SALES'

when saleschannel=online and userid like '%RB:MOBILE%' then 'MOBILE'

when userid like '%RB:MOBILE%' and userid not in ('RB:MOBILEWINDOWASAPP','RB:MOBILEADRIODAPP') then 'WAP SALES'

when userid like '%RB:MOBILE%' and userid in ('RB:MOBILEWINDOWASAPP','RB:MOBILEADRIODAPP') then 'APP SALES'

when saleschannel='corporate' and bookingsource='corporate' then 'offline agents'

when saleschannel='Corporate' and bookingsource='webdirect' then 'other offline agents'

end as channel

can you help out with this Query ..i am facing problem in writing (in function) and not like

Re: CASE statement in QlikView

Thu, 03 Sep 2015 12:17:36 GMT

Hi All,

I've been also struggling to convert this piece of code to Qlikview,

Could I please get assistance:

CASE

when MAC_CODE = 963 then ''SNL''

Else ''MLOAN''

END as PRODUCT_CODE

CASE

when REGISTRATION_MONTH_CODE = ''NOTREG''

then ''N''

else

CASE when ( year(INFORMATION_DATE)*100 + month(INFORMATION_DATE) )

else ''N''

END as REG_LAST_3MONTHS_IND

CASE

when FINAL_ML_AMT <= 50000 then ''A''

when FINAL_ML_AMT <= 100000 then ''B''

when FINAL_ML_AMT <= 150000 then ''C''

when FINAL_ML_AMT <= 350000 then ''D''

when FINAL_ML_AMT <= 700000 then ''E''

else ''F''

END as MLOANS_BANDS_CODE

Re: CASE statement in QlikView

Sun, 29 Nov 2015 10:24:23 GMT

Try this:

https://community.qlik.com/message/922066#922066

Re: CASE statement in QlikView

Sun, 29 Nov 2015 14:28:58 GMT

fix:

=if(wildmatch([Account Name],'*Hong Kong*'), 'HK', if(wildmatch([Account Name],'*USA*'), 'NY', if(wildmatch([Account Name],'*UK*'),'LDN', 'OTHER')))

Re: CASE statement in QlikView

joshrussin — Wed, 04 Oct 2017 17:32:06 GMT

I am trying to do this, but trying to use some of the same values for different cases.

Ex.

=if(WildMatch(SaTy_OTIF,'2MEM','2MMO','NB2','ZBPU','ZD2S','ZKR'),'Internal / Warehouse to Warehouse',

if(WildMatch(SaTy_OTIF,'ZEU1','ZIN1'),'OUS',

if(WildMatch(SaTy_OTIF,'OR','UB'),'Domestic',

if(WildMatch(SaTy_OTIF,'2MEM','2MMO','NB2','ZBPU','ZD2S','ZKR','ZEU1','ZIN1','OR','UB'),'test'))))

This only shows the first 3 and not the "total" statement.

Re: CASE statement in QlikView

shikha26 — Wed, 02 Sep 2020 13:38:28 GMT

Hello ,

I have few qlikview doubts, last time, Tilak told me that you can help

The doubt is:

There is a field Current_status_cd , it has 5 values (Accepted,Activated,Cancelled,Ready For Activation,Requested).

Screenshot in qlikview:

I want to create one fields using this field which should contain two values (Brutoo and netto) as a list box

Brutoo = means all status selected

Netto = means all status excluding cancelled

Brutto: Netto:

I am writing if conditions but no conditions is giving expected result.

if(CURRENT_STATUS_CD <> 'Cancelled','Netto'

,if(CURRENT_STATUS_CD = 'Accepted' or CURRENT_STATUS_CD = 'Activated'

or CURRENT_STATUS_CD = 'Ready For Activation' or CURRENT_STATUS_CD = 'Requested' or CURRENT_STATUS_CD = 'Cancelled' ,'Brutto',0)) AS Netto_brutoo_filter

But no condition is giving expected result, Requirement is user want whole dashboard to change on netto and brutto switch.

Please help and let me know, if any platform where I can raise my doubts

Re: CASE statement in QlikView

shikha26 — Wed, 02 Sep 2020 14:15:31 GMT

The doubt is:

There is a field Current_status_cd , it has 5 values (Accepted,Activated,Cancelled,Ready For Activation,Requested).

Screenshot in qlikview:

I want to create one fields using this field which should contain two values (Brutoo and netto) as a list box

Brutoo = means all status selected

Netto = means all status excluding cancelled

Brutto: Netto:

I am writing if conditions but no conditions is giving expected result.

if(CURRENT_STATUS_CD <> 'Cancelled','Netto'

,if(CURRENT_STATUS_CD = 'Accepted' or CURRENT_STATUS_CD = 'Activated'

or CURRENT_STATUS_CD = 'Ready For Activation' or CURRENT_STATUS_CD = 'Requested' or CURRENT_STATUS_CD = 'Cancelled' ,'Brutto',0)) AS Netto_brutoo_filter

But no condition is giving expected result, Requirement is user want whole dashboard to change on netto and brutto switch.

Please help and let me know,

Kindly assist.

Thanks,

Shikha