Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Vitali
Contributor III
Contributor III

Table disappears in Qlik Sense when interpreting a date field

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.

Labels (4)
12 Replies
tomovangel
Partner - Specialist
Partner - Specialist

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
Contributor III
Contributor III
Author

Thank you, I'll try that right away. You could be on to something here...
papanovn
Partner - Contributor II
Partner - Contributor II

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
Contributor III
Contributor III
Author

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
Partner - Specialist
Partner - Specialist

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
Partner - Champion III
Partner - Champion III

>>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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Vitali
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Partner - Specialist
Partner - Specialist

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