Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

martin_hamilton
Contributor

Calculate a status by evaluating 2 fields in load script

Hi - I have a formula in my load script which allows me to create a new field based on the contents of other fields:

IF(0 <= (SUBS_DISCONNECTED_DATE) , 'Disconnected', 'Connected') as SUBSCRIPTION_STATUS

so in the below example it generates the following:

SUBS_CONNECTED_DATESUBS_DISCONNECTED_DATESUBSCRIPTION_STATUS
2017-01-01Connected
2017-01-012017-10-10Disconnected
Not Connected

But when the SUBS_DISCONNECTION_DATE is blank AND the SUBS_CONNECTION is blank I want it to state that the SUBSCRIPTION_STATUS is Not Connected.

Any guidance appreciated.

thanks

martin

1 Solution

Accepted Solutions
mark6505
Valued Contributor III

Re: Calculate a status by evaluating 2 fields in load script

Hi,

My normal approach is to use TRIM() and LEN() functions as covers possible problems with the field have a space and actually NULL.

IF(LEN(TRIM(SUBS_DISCONNECTED_DATE)) = 0 AND LEN(TRIM(SUBS_CONNECTED_DATE)) = 0,

       'Not Connected',

      IF(LEN(TRIM(SUBS_DISCONNECTED_DATE)) = 0 AND LEN(TRIM(SUBS_CONNECTED_DATE)) = 1,

          'Connected',

          'Disconnected'))   as SUBSCRIPTION_STATUS

4 Replies
OmarBenSalem
Esteemed Contributor

Re: Calculate a status by evaluating 2 fields in load script

maybe

if(isnull(SUBS_DISCONNECTION_DATE) or SUBS_DISCONNECTION_DATE='' and SUBS_CONNECTION='' or isnull(SUBS_CONNECTION), 'Disconnected', IF(0 <= (SUBS_DISCONNECTED_DATE) , 'Disconnected', 'Connected') ) as SUBSCRIPTION_STATUS

daveamz01
Contributor III

Re: Calculate a status by evaluating 2 fields in load script

Hi Martin,

IF(Len(Trim(SUBS_CONNECTION_DATE))>0 AND Len(Trim(SUBS_DISCONNECTED_DATE)) = 0   'Connected', 'Disconnected') as SUBSCRIPTION_STATUS

I've used Trim() because I'm not sure if your fields are null or blank.

Regards,

David

mark6505
Valued Contributor III

Re: Calculate a status by evaluating 2 fields in load script

Hi,

My normal approach is to use TRIM() and LEN() functions as covers possible problems with the field have a space and actually NULL.

IF(LEN(TRIM(SUBS_DISCONNECTED_DATE)) = 0 AND LEN(TRIM(SUBS_CONNECTED_DATE)) = 0,

       'Not Connected',

      IF(LEN(TRIM(SUBS_DISCONNECTED_DATE)) = 0 AND LEN(TRIM(SUBS_CONNECTED_DATE)) = 1,

          'Connected',

          'Disconnected'))   as SUBSCRIPTION_STATUS

martin_hamilton
Contributor

Re: Calculate a status by evaluating 2 fields in load script

Hi Mark

Made a slight amendment based on my own data to evaluate a value greater than zero rather than a equal to 1.

IF(LEN(TRIM(SUBS_DISCONNECTED_DATE)) = 0 AND LEN(TRIM(SUBS_CONNECTED_DATE)) = 0,

       'Not Connected',IF(LEN(TRIM(SUBS_DISCONNECTED_DATE)) = 0 AND LEN(TRIM(SUBS_CONNECTED_DATE)) >= 0,'Connected',

          'Disconnected'))   as SUBSCRIPTION_STATUS_4,

Many Thanks

Martin

Community Browser