Table disappears in Qlik Sense when interpreting a date field

[Vitali]

Hi there,

We're experiencing a recurring problem - when pulling data from a SQL Server, Qlik doesn't interpret the date field as a date. When we try to rectify this by using the interpreter function (Date#), the whole table disappears. We've accidentally had this issue on different machines and by different users, so it's not limited to my machine, it is something else. 

End goal is quite simple - to put that date field in a Date Picker object. However, it appears we're missing something and we don't quite know what. We've even changed the default date format (through the script editor) to match the SQL Date format in the hope that this will help but to no avail.

Any suggestions are welcome.

Thank you in advance.

[tomovangel]

Hello, 

When I use dates in my analysis I always floor them first in the script editor. 

I use this function, you can try it 

date(floor(date_field),'DD.MM.YYYY') as date_field

You can try, and the format specified in '' can be anything you want like dd/mm/yyyy or another one. 

Can you try?

BR,

-Ace

[Vitali]

Thank you, I'll try that right away. You could be on to something here...

[papanovn]

I always convert dates to numbers in the script. Otherwise set analysis filter by date variable fails time to time. Then I use field format in the objects to show the numbers in the appropriate date format.

[Vitali]

Alright, so just to make it clear for myself:

- SQL contains a date field, for this example it's called Date_Field (lets imagine it is like YYYY-MM-DD)

- QlikSense, for whatever reason, doesn't recognize this field as a date;

- I should convert it to  a data with an interpreter function - Date#(Date_Field, 'YYYY-MM-DD');

- Then, I should format it to my preferred format (say, DD-MMM-YY) with the the formatting function -

Date(Date#(Date_Field, 'YYYY-MM-DD'), 'DD-MMM-YY'))

Is this correct?

P.S. I'm aware of the Floor/Timestamp approach that should be used if the SQL Field is actually a timestamp and not a date field.

[tomovangel]

This approach is too long for me, i do 1 of two things: 

1. When getting data from sql, i store it to QVD, then load the qvd with FLOOR function on all date fields, to my client's preffered format. 

2. When getting data from sql i store it to qvd, then load from the qvd all date fields with MakeDate function.

[jonathandienst]

>>Date(Date#(Date_Field, 'YYYY-MM-DD'), 'DD-MMM-YY'))
>>Is this correct?

Yes. This is the correct syntax. But as an alternative, you could get QS to recognize the date by adjusting the environment variables to set the date format to YYYY-MM-DD.

[Vitali]

That makes sense. Tricky thing is that you have to actually check the SQL field table as a timestamp can be sometimes visualized as a simple date - so without flooring the values, even this wouldn't work.

The other tricky thing is that in the SQL DB different 'date' fields may have different format and there can be the case that we pull two or more SQL tables/views into QS, where each source table has slightly different layout/format.  So, as far as I understand, the only way to be perfectly safe and ensure all date fields can be interpreted properly, is to make this transformation on each that I plan to use.

[Vitali]

Perhaps it was a bug, but a field, built with MakeDate wasn't recognized by the Date Picker.

I noticed that occasionally, when working with Qlik Sense Cloud, the app doesn't recognize the changes made in the data, unless I close the tab in the browser and reopen it again. Perhaps many of my confusions stem from there...

As for QVD , I have to work with QS, as this is the client's requirement here.

[tomovangel]

You can use QVD in QLik sense as well :). Because sometimes you have sql data which is static, and does not change and if you store it to a qvd it will read 10x times faster