replicate case statement in qlikview

[joshibabu_are]

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

       END

          "Effective_Cert_Status",

Thanks,

Joshi

[sunny_talwar · Accepted solution]

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....

[stabben23]

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

[maxgro]

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;

[jpenuliar]

A switch might work for you.

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