topic Re: Table disappears in Qlik Sense when interpreting a date field in App Development

Table disappears in Qlik Sense when interpreting a date field

Vitali — Sat, 16 Nov 2024 20:51:32 GMT

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.

Re: Table disappears in Qlik Sense when interpreting a date field

tomovangel — Thu, 16 May 2019 07:16:15 GMT

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

Re: Table disappears in Qlik Sense when interpreting a date field

Vitali — Thu, 16 May 2019 07:25:10 GMT

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

Re: Table disappears in Qlik Sense when interpreting a date field

papanovn — Thu, 16 May 2019 13:34:46 GMT

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.

Re: Table disappears in Qlik Sense when interpreting a date field

Vitali — Fri, 17 May 2019 09:50:23 GMT

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.

Re: Table disappears in Qlik Sense when interpreting a date field

tomovangel — Fri, 17 May 2019 10:21:12 GMT

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.

Re: Table disappears in Qlik Sense when interpreting a date field

jonathandienst — Fri, 17 May 2019 11:14:04 GMT

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

Re: Table disappears in Qlik Sense when interpreting a date field

Vitali — Fri, 17 May 2019 12:47:26 GMT

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.

Re: Table disappears in Qlik Sense when interpreting a date field

Vitali — Fri, 17 May 2019 12:52:46 GMT

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.

Re: Table disappears in Qlik Sense when interpreting a date field

tomovangel — Fri, 17 May 2019 12:54:39 GMT

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

Re: Table disappears in Qlik Sense when interpreting a date field

Vitali — Fri, 17 May 2019 13:15:12 GMT

Alright, here I'm confused:
https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/QVD_files.htm

Re: Table disappears in Qlik Sense when interpreting a date field

tomovangel — Fri, 17 May 2019 13:18:09 GMT

Here you go 🙂

https://help.qlik.com/en-US/sense/April2019/Subsystems/Hub/Content/Sense_Hub/Scripting/work-with-QVD-files.htm

Re: Table disappears in Qlik Sense when interpreting a date field

Vitali — Fri, 17 May 2019 14:15:08 GMT

Thanks a lot 🙂

I'm still unsure how or why the date fields messed up the table, but at least I know now how to process date fields on input (and I've learned about QVD files too 🙂 ).

Thank you @tomovangel , @jonathandienst and @papanovn !