If statement in script only evaluating when 2 fields present
I have a table of orders and based on the client status and termination date I need to set a flag to show whether that was an active order on the period end date.
I'm adding the status and termination date to my order list from the customer list with the intention of dropping those fields from the Order list after the flag has been set.
However, my if statement is only evaluated when there is a value for both the ACVContractTermination and the ACVClientStatus. I have tried trimming the values, testing for nulls etc. to no avail. Even if my if statement doesn't make any reference to ACVContractTermination (2nd commented out line) it will only work if that field has been populated. I ran the script excluding the ACVContractTermination column entirely and then an if statement on ACVClientStatus worked, but as soon as it was added back same problem cropped up.
Any ideas on what is causing this behaviour and how I can get around it?
Script snippet below.
Thanks,
Lorna
left join (ACVOrders)
Load
CustomerID,
ContractTermination as ACVContractTermination,
Client_Status__c as ACVClientStatus
Resident Customer;
left join (ACVOrders)
Load
*,
//if(trim(ACVClientStatus)='Current',1,if(trim(ACVClientStatus)='Former' and ACVContractTermination>ACVDate,1,0)) as CustActiveOnACVDate
//if(ltrim(rtrim(ACVClientStatus))='Current',1,0) as CustActiveOnACVDate
if(ACVContractTermination='',1,0) as CustActiveOnACVDate
I've worked around the issue but I don't really understand why I had to go this route.
I created my ACVOrders table as an ACVOrdersTemp and then when creating the flag did this as a new table called ACVOrders using resident load of ACVOrdersTemp. So I can't do the if logic when left joining a resident load to the resident table?
left join (ACVOrdersTemp)
Load
CustomerID,
ContractTermination as ACVContractTermination,
Client_Status__c as ACVClientStatus
Resident Customer;
ACVOrders:
Load
*,
if(trim(ACVClientStatus)='Current' or len(ACVClientStatus)=0,1,if(trim(ACVClientStatus)='Former' and ACVContractTermination>ACVDate,1,0)) as CustActiveOnACVDate
//if(ltrim(rtrim(ACVClientStatus))='Current',1,0) as CustActiveOnACVDate
//if(len(ACVContractTermination)=0,1,0) as CustActiveOnACVDate
