Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Thank you!
Loren Dean-Austin
Hi,
You might try something like this in a preceding load:
load
contract_end_date,
contractID,
if(date#(contract_end_date)<=today(), 'contract ended', 'contract current') as contractStatus;
data:
load * inline [
contract_end_date, contractID
'12/03/2015', 1
'12/21/2016', 2
];
This example gives the following result
Hi Loren
Index() is not the right function, i would suggest you to go with the "if" condition instead.
Hi Sangram,
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?
Thanks
Loren
Hi Loren,
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
Hi Sangram
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?
Thank you
Loren
Loren Dean-Austin
Customer Insight Analyst
Business Excellence
ForViva
52 Regent Street
Eccles
M30 0BP
T: 0161 605 7921
E: loren.dean-austin@forviva.co.uk<mailto:loren.dean-austin@forviva.co.uk>
W: www.forviva.co.uk<http://www.forviva.co.uk>