Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator III
Creator III

New field in script based on date

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Try it with:

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

- Marcus

View solution in original post

5 Replies
MVP & Luminary
MVP & Luminary

Try it with:

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

- Marcus

View solution in original post

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
Not applicable

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.

Master II
Master II

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

Best Regards,
KC
Creator III
Creator III

Thanks Marcus, that worked perfectly.

Best wishes,

Jes