4 Replies Latest reply: Oct 31, 2017 11:37 AM by Martin Hamilton

# 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

• ###### 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

• ###### 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

• ###### 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

• ###### 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