Hello, I wonder if you can help?
I have a data set where the end date of a contract is recorded as a date for those whose contract has ended, and as a blank field (but it seems to be in date format) for those whose contract hasn't ended. I want to load this into my app as 'Contract Ended' or 'Current Contract'
I tried the following expression:
If(Index("contract_end_Date",(<=07/12/2015)), "Contract Ended", 'Current Contract'),
But when the script loads is says : ')' expected.
I am basing this on a piece of scripting that has worked in the same app which was as follows:
If(Index(e_mail, '@'), 'Has email', 'No email'),
Can anyone help?
You might try something like this in a preceding load:
if(date#(contract_end_date)<=today(), 'contract ended', 'contract current') as contractStatus;
load * inline [
This example gives the following result
Index() is not the right function, i would suggest you to go with the "if" condition instead.
Thank you for this. I have tried to use the below, with no luck (it doesn't recognise anything as a 'contract ended', and thinks that everything in the app is 'contract current')
if(date#(contract_end_dt)<=today(), 'contract ended', 'contract current') as TenancyStatus,
Would this have been the way you would have written the formula using the "if" condition?
You have not mentioned the format of the date in the date# function.
try this: if(date#(contract_end_dt,'MM/DD/YYYY')<=today(), 'contract ended', 'contract current') as TenancyStatus,
just replace the 'MM/DD/YYYY' with the format of your date string and try please
I have tried this, unfortunately it still hasn’t worked. It still recognises every customer as ‘contract current’, even though there are at least half that have a null value in the contract_end_dt field.
Is it because they are null values that this isn’t working?
Customer Insight Analyst
52 Regent Street
T: 0161 605 7921