New field in script based on date

[jessica_webb]

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

[marcus_sommer · Accepted solution]

Try it with:

IF(Date([Latest date]) < '$(vCut_off_date)',1,0) as [Date set],

- Marcus

[sunny_talwar]

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);

[Anonymous]

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.

[jyothish8807]

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

[jessica_webb]

Thanks Marcus, that worked perfectly.

Best wishes,

Jes