Understanding the nuances of dates in Qlik

[wdchristensen]

I am trying to understand why a formula like the one below will work on a date field returned by a function but if that “same” value is pulled from a resident table it returns a null value. After some trial and error, I determined you can use the Num# function instead of the Date# function if the value is pulled from a resident table. Please help me understand "why". I appreciate your help! 

SET DateFormat='YYYY-M-D';

Test_01:
LOAD
today() as bToday, RecNo() as Num
autogenerate 1
;

Test_02:
Noconcatenate
Load
Date(Num#(bToday,'0.0000'), 'M/D/YYYY') as success_Today_01
,Date(Date#(today(),'YYYY-M-D'), 'M/D/YYYY') as success_Today_02
,Date(Date#(bToday,'YYYY-M-D'), 'M/D/YYYY') as fail_Today_01
,Date(Date#(Num#(bToday),'YYYY-M-D'), 'M/D/YYYY') as fail_Today_02
,Num
Resident Test_01;

Exit Script;

[wdchristensen]

Hey Buddy, 

If you use the "date" as follows then your "fails" will succeed. 

date(today(), 'YYYY-M-D')

reference 01546982

This method will cause Date(Num#(_Today,'0.0000'), 'M/D/YYYY') as success_Today_01 to fail since the stored value is now a string and not a dual. 

[jpenuliar]

Hi,

It seems that Date# is returning a text type, when fed to Date()  this in turn returns a null.

numeric or text?

[wdchristensen]

My limited understanding is that dates in Qlik Sense are dual in nature (String and Numeric). 

date#() is supposed to return a dual. 

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

[jpenuliar]

I thought the same.
Even when you look at the field in Data Model Viewer, it's been tagged as $numeric, $text etc...
however, if you evaluate type using isnum() or istext() as shown in the snapshot, it gives you the actual interpretation of data.

[rwunderlich · Accepted solution]

I'll take a stab at answering the question as to "why".  But first I'd like to point out that both:

Date(Date#(Today(),'YYYY-M-D'), 'MM/DD/YY') as Date1,
Date(Date#(bToday,'YYYY-M-D'), 'MM/DD/YY') as Date2

are incorrect usage.  It makes no sense to parse these values as strings.  If you want to change the date format from default, the correct syntax would be:

Date(Today(), 'MM/DD/YY') as Date1A,
Date(bToday, 'MM/DD/YY') as Date2A

So I would say the best solution is "don't do that" but you have posed an interesting academic question I think I can answer. 

Date#() expects string input. Today() returns a dual value. Duals contain both a string and numeric representation and in most cases Qlik is smart enough to select the correct representation for the use. Your default date format is 'YYYY-M-D'. As Date#() expects string, the string value of the dual is used and parsing it as 'YYYY-M-'D' works. 

Duals can be expensive in terms of RAM. Storing both numeric and string for every value can use up a lot of storage. 

43474|2010-1-9
43475|2010-1-10
etc..

As a storage optimization when default date formatting is used, Qlik does not store the string of each value in the data table.  Instead it attaches the date format as an attribute of the field and formats on-demand as needed. See https://qlikviewcookbook.com/2017/03/dual-storage-vs-dual-behavior/

today(1) as bToday

will store bToday as number only with the Field Format attribute applied as required, for example when showing in a listbox. But...it appears that loading the field directly in script does not apply the formatting.  So you cannot rely on default formatting if you need the string value in script. 

Either of these modifications to your script will give correct results.

Create field with: 
Date(today()) as bToday

Format before parsing:
Date(Date#(Date(bToday),'YYYY-M-D'), 'MM/DD/YY') as Date2

The latter example is more storage efficient as the data model storage will contain only the numeric for bToday. 

Again, I want to emphasize for beginners, none of these are examples are the correct way.  If you want to assign a date format to data that is already a numeric date, use Date(field, 'format')

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[wdchristensen]

Hi Rob,

Thanks for taking the time to explain what is happening with these dual fields. Your article does a good job of clarifying some of the anomalies I am experiencing.

[rwunderlich]

Yes, it's an odd and sometimes useful thing that a date can only have one format associated with the field.  See https://qlikviewcookbook.com/2016/07/touchless-formatting/

-Rob

[wdchristensen]

Your link answers my other question! Thanks so much for your help. 

https://community.qlik.com/t5/Qlik-Sense-App-Development/Reformatting-date-fields-1-at-a-time-Must-b...