Calculate a status by evaluating 2 fields in load script

[martin_hamilton]

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_DATE	SUBS_DISCONNECTED_DATE	SUBSCRIPTION_STATUS
2017-01-01		Connected
2017-01-01	2017-10-10	Disconnected
		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

[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]

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 · Accepted solution]

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]

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