Create field based on date

[khaycock]

Is it possible to create a field based on a date? I wanted to create a field that shows deciphers whether data is pre-2019 or post-2019.

The date field I'm using has the format MMM DD, YYYY hh:mm:ss so would need to be split at Jan 01, 2019, 00:00:00 if possible?

[dplr-rn · Accepted solution]

Not 100% clear what you need.
but try something like below in your load script
if(Year(DATEFIELD)=2019
, 2019
, if(Year(DATEFIELD)<2019,'Pre 2019','Post 2019')
) as 2019_Flag

[skamath1]

You can use the script function Date# to convert the text in date. 

Example 

Date(Floor(Num(Date#(Datefield, 'MMM DD, YYYY hh:mm:ss'))) )

The Num and floor function will remove the decimal portion, and the Date function will convert to a date field with the format as specified in the set. 

Check the help link.

https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/Interpreta...

https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Introduction/convent...

[khaycock]

I had to force the date to a normal format 'DD/MM/YYYY' and then this worked! Thanks 

[khaycock]

Is there a way of future proofing this bit of scripting, so that anything that is 2019 onwards (including any future years too) rather than just hard coding to 2019 specifically?

[dplr-rn]

Sure. I am assuming it is always current year.
if so change my code to something like below (hastily typed pls check syntax)

if(Year(DATEFIELD)=Year(today())
, Year(today())
, if(Year(DATEFIELD)<2019,'Pre '&Year(today()),'Post '&Year(today()))
) as CurrentYear_Flag

[khaycock]

No unfortunately they want it for everything from 2019 onwards, so there may still be 2019 data when we are in 2020 therefore I can't do the current year

[dplr-rn]

Ok.
then put your logic into a variable which will populate the year as you require (if it can be done problematically do that or read from a excel file or something which can be changed)
use the variable instead of the Year(today()) in above logic

[khaycock]

So I can't force it to look for the year that is 2019 or higher in the standard script?

[dplr-rn]

Not sure what you mean.
please explain the business logic behind the flag .