topic Re: replicate case statement in qlikview in QlikView

replicate case statement in qlikview

joshibabu_are — Tue, 07 Feb 2017 11:30:31 GMT

hi guru's,

i want to implement case statement from oracle table into Qlikview script.

how to achieve this.

CASE

WHEN CERT_REQUIRED IS NULL

THEN

'Data Issue'

WHEN CERT_REQUIRED = 'No'

THEN

'No Cert Required'

WHEN CERT_REQUIRED = 'Yes'

THEN

CASE

WHEN cert_expiration IS NULL THEN cert_status ||''|| ' - Certificate – NO Expiry'

WHEN cert_expiration < SYSDATE THEN 'Expired Certificate'

WHEN cert_expiration < SYSDATE + 30 THEN 'Expiring 30 Days'

WHEN cert_expiration < SYSDATE + 90 THEN 'Expiring 90 Days'

WHEN cert_expiration < SYSDATE + 180 THEN 'Expiring 180 Days'

ELSE 'Certificate in Good Standing'

END

"Effective_Cert_Status",

Thanks,

Joshi

Re: replicate case statement in qlikview

sunny_talwar — Tue, 07 Feb 2017 12:11:55 GMT

May be this:

If(Len(Trim(CERT_REQUIRED)) = 0, 'Data Issue',

If(CERT_REQUIRED = 'No', 'No Cert Required',

If(CERT_REQUIRED = 'Yes',

If(Len(Trim(cert_expiration)) = 0, cert_status & ' - Certificate – NO Expiry',

If(cert_expiration < Today(), 'Expired Certificate',

If(cert_expiration < Today() + 30, 'Expiring 30 Days',

If(cert_expiration < Today() + 90, 'Expiring 90 Days',

If(cert_expiration < Today() + 180, 'Expiring 180 Days', 'Certificate in Good Standing')))))))) as [Effective_Cert_Status]

I might have added an extra parenthesis or missed one....

Re: replicate case statement in qlikview

stabben23 — Tue, 07 Feb 2017 12:29:43 GMT

This could be an alternative:

if(isnull(CERT_REQUIRED), 'Data Issue')),

If(CERT_REQUIRED ='No','No Cert Required',

if(CERT_REQUIRED ='Yes',

if(cert_expiration < today(),'Expired Certificate',

if(today() - cert_expiration > 30 , 'Expiring 30 Days',

if(today() - cert_expiration > 90 , 'Expiring 90 Days',

if(today() - cert_expiration > 180 , 'Expiring 180 Days',

Certificate in Good Standing'))))))) as "Effective_Cert_Status"

Be aware also here With parentheses

Re: replicate case statement in qlikview

maxgro — Tue, 07 Feb 2017 12:29:51 GMT

maybe with a nested if, something like (replace the variables with your fields, sysdate with today() function)

let cert_required = 3;

let cert_expiration = 3;

load

if ($(cert_required)=1, 'cert_required 1',

if ($(cert_required)=2, 'cert_required 2',

if ($(cert_required)=3,

if ($(cert_expiration)=1, 'cert_required 3, cert_expiration 1',

if ($(cert_expiration)=2, 'cert_required 3, cert_expiration 2',

if ($(cert_expiration)=3, 'cert_required 3, cert_expiration 3',

if ($(cert_expiration)=4, 'cert_required 3, cert_expiration 4'

)))),

if ($(cert_required)=4, 'cert_required 4',

'cert_required other'

)))) as effective_cert_status

AutoGenerate 1;

Re: replicate case statement in qlikview

jpenuliar — Tue, 07 Feb 2017 15:50:27 GMT

A switch might work for you.

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/ScriptControlStatements/Switch.htm