Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
martin_hamilton
Creator
Creator

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
Mark_Little
Luminary
Luminary

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

View solution in original post

4 Replies
OmarBenSalem

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

daveamz
Partner - Creator III
Partner - Creator III

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

Mark_Little
Luminary
Luminary

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
Creator
Creator
Author

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