topic Re: New field in script based on date in QlikView

New field in script based on date

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: New field in script based on date

marcus_sommer — Fri, 23 Sep 2016 07:14:40 GMT

Try it with:

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

- Marcus

Re: New field in script based on date

sunny_talwar — Fri, 23 Sep 2016 07:15:12 GMT

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

Re: New field in script based on date

Anonymous — Fri, 23 Sep 2016 07:22:13 GMT

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.

Re: New field in script based on date

jyothish8807 — Fri, 23 Sep 2016 07:29:55 GMT

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

Re: New field in script based on date

jessica_webb — Fri, 23 Sep 2016 07:30:38 GMT

Thanks Marcus, that worked perfectly.

Best wishes,

Jes