topic Re: Create field based on date in QlikView

Create field based on date

khaycock — Sat, 16 Nov 2024 03:58:48 GMT

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?

Re: Create field based on date

dplr-rn — Tue, 02 Apr 2019 14:12:09 GMT

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

Re: Create field based on date

skamath1 — Tue, 02 Apr 2019 14:12:14 GMT

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/InterpretationFunctions/date_hash.htm

https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Introduction/conventions-number-time-formats.htm

Re: Create field based on date

khaycock — Tue, 02 Apr 2019 15:46:30 GMT

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

Re: Create field based on date

khaycock — Thu, 04 Apr 2019 15:12:41 GMT

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?

Re: Create field based on date

dplr-rn — Thu, 04 Apr 2019 15:15:48 GMT

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

Re: Create field based on date

khaycock — Thu, 04 Apr 2019 15:16:59 GMT

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

Re: Create field based on date

dplr-rn — Thu, 04 Apr 2019 15:20:20 GMT

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

Re: Create field based on date

khaycock — Thu, 04 Apr 2019 15:23:55 GMT

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

Re: Create field based on date

dplr-rn — Thu, 04 Apr 2019 15:28:08 GMT

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

Re: Create field based on date

khaycock — Thu, 04 Apr 2019 15:49:56 GMT

My dashboard displays loads of security patches and their 'First Observed Date' field. I need to have a field that allows the user to drill using a field whether the patch was first observed before 31/Dec/2018 or after 01/Jan/2019 (new management processes went into access on Jan 1st so need to see the difference between all the data before 2019 and for all the data that comes after

Re: Create field based on date

dplr-rn — Thu, 04 Apr 2019 15:53:37 GMT

what happens when you move current date becomes 2020?

Re: Create field based on date

khaycock — Thu, 04 Apr 2019 16:07:18 GMT

I was hoping we could force it to be anything >=2019. So anything that is equal to or larger than 2019 it would pick it up, therefore picking up 2019 and 2020?

Re: Create field based on date

dplr-rn — Thu, 04 Apr 2019 16:09:26 GMT

wont the current logic already pickup 2020 data and mark it as 'Post 2019'?

Re: Create field based on date

khaycock — Fri, 05 Apr 2019 07:40:51 GMT

I wasn't sure because of the first half of the expression you gave me..

"if(Year(date(FirstDiscoveredAgain))= '2019',2019,"

Does that not make it equals to 2019 only?

Re: Create field based on date

dplr-rn — Fri, 05 Apr 2019 13:09:54 GMT

No
you are create a new column/dimension 2019_Flag while checking year of your date. see below in green

if(Year(DATEFIELD)=2019 //if Year of your date field is equal to 2019
, 2019 // then 2019
, if(Year(DATEFIELD)<2019, //if Year of your date field is less than 2019

'Pre 2019','Post 2019') //then Pre 2019 else Post 2019
) as 2019_Flag

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

Re: Create field based on date

dplr-rn — Fri, 05 Apr 2019 13:11:36 GMT

I would suggest you check out some qlik sense course in udemy
they are fairly inexpensive