Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am trying to create a new field in my script, based on dates in my data file, and a date set as a variable.
I have created a variable in my script as follows:
Set vCut_off_date = 01/09/2015;
Later in the script I load a table with the following values:
LOAD
Type,
IF(Date([Latest date]) < Date($(vCut_off_date)),'1','') as [Date set],
[Latest date],
Overall
FROM
[..\..\SourceData\All_Data\QVDs\Cut offs.qvd]
(qvd);
IF(Date([Latest inspection date])< Date($(vOfsted_date)),'1','') as [Date set],
My 'If' statement doesn't give me any values for 'Date set' field.
What I'm trying to do is look at the 'Latest date' column, if it is before 01/09/2015, I would like to give it a value of '1' in the new 'Date set' field. Otherwise, I would like it to be blank/null.
I've tried a few variations of my 'if' statement, but none of them work!
Any advice would be appreciated.
Many thanks,
Jess
Try it with:
IF(Date([Latest date]) < '$(vCut_off_date)',1,0) as [Date set],
- Marcus
Try it with:
IF(Date([Latest date]) < '$(vCut_off_date)',1,0) as [Date set],
- Marcus
Try this:
LET vCut_off_date = Num(MakeDate(2015, 9, 1));
LOAD
Type,
If([Latest date] < $(vCut_off_date),1,'') as [Date set],
[Latest date],
Overall
FROM
[..\..\SourceData\All_Data\QVDs\Cut offs.qvd]
(qvd);
Change this row (' added around variable)
IF(Date([Latest date]) < Date('$(vCut_off_date)'),'1','') as [Date set],
if still no success, enter this Expression in a TextBox while seletincg one value of Latest date in a listbox
do it step by step, first the second part Date('$(vCut_off_date)'), then the first part Date([Latest date])
look if Dates are in same Format, and finally Combine both parts
sometimes there are minor erros in Expression. I can correct it in TextBox and when Expression works, I take
the Expression in script etc.
Hi Jesicaa,
Please make sure the format of "Latest date" and $(vCut_off_date)" are in same format . eg Both are DD/MM//YYYY format.
Regards
KC
Thanks Marcus, that worked perfectly.
Best wishes,
Jes