Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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