Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Ruthie09
Contributor III
Contributor III

Date/Time formatting in If statement

Please help.  I can't seem to get my table dates to appear in the proper format when using an "if" statment

  • I have table called TimeZones (which lists all the Qlik-compatible time zone locations).
  • I have a variable called SelectedTimeZone that uses the GetFieldSelections function to return the value selected by the user in a single-select filter called TimeZoneRegions.
  • I have a table of help desk tickets that, for testing purposes, contains the created date & time for each ticket (SYS_CREATED_ON) and a field called Modified Created that should show the SYS_CREATED_ON converted to the user-selected UTC offset time.

For the most part, the conversion works correctly.  When I set Modified Created formula to be only =ConvertToLocalTime(SYS_CREATED_ON, '$(SelectedTimeZone)'), the Modified Created column in the table is formatted correctly (and displays the applicable value based on the location selected)

However, when I set the Modified Created field to display the SYS_CREATED_ON value if nothing is selected in the TimeZoneRegions filter (see formula below), the column displays in numeric decimal format vs standard date/time format (regardless of whether or not a value is selected).

=if(isnull(TimeZoneRegions), SYS_CREATED_ON, ConvertToLocalTime(SYS_CREATED_ON, '$(SelectedTimeZone)')) 

clipboard_image_1.png

 

How do I get it to display correctly?

1 Solution

Accepted Solutions
gmenoutis
Partner - Creator II
Partner - Creator II

Just use date(SYS_CREATED_ON).

The function ConvertToLocalTime expects a timestamp as first argument, and is therefore able to read the (truly numeric) value as a date. Then it returns a dual value, which actually has the numeric value in the numeric part, but it also has in the text part the human-friendly version.

On the contrary, when you just read SYS_CREATED_ON, you are reading a numeric value. You have to use date() in order to make the engine use the aformentioned dual functionality.

View solution in original post

2 Replies
gmenoutis
Partner - Creator II
Partner - Creator II

Just use date(SYS_CREATED_ON).

The function ConvertToLocalTime expects a timestamp as first argument, and is therefore able to read the (truly numeric) value as a date. Then it returns a dual value, which actually has the numeric value in the numeric part, but it also has in the text part the human-friendly version.

On the contrary, when you just read SYS_CREATED_ON, you are reading a numeric value. You have to use date() in order to make the engine use the aformentioned dual functionality.

Ruthie09
Contributor III
Contributor III
Author

I did modify your solution a bit by using timestamp() vs date() because I need the time to be displayed.  Thank you for helping me.

Also, I changed it so the first value in the list of TimeZoneRegions was "- none -" and set that to force the default value to be equiv to SYS_CREATED_ON (since it kept trying to change the time by 2 hours even when no value was selected).

=if(TimeZoneRegions='- none -', timestamp(ConvertToLocalTime(SYS_CREATED_ON,'GMT')), timestamp(ConvertToLocalTime(SYS_CREATED_ON, '$(SelectedTimeZone)')))