topic Re: Time as Axis, formatted AM/PM , but with granularity of milliseconds. in App Development

Time as Axis, formatted AM/PM , but with granularity of milliseconds.

johnnymartinez — Sat, 16 Nov 2024 19:28:48 GMT

So a picture is worth a thousand words -

I'm recording activity on our SQL server to show high resource / long running queries daily/weekly etc.

I'm trying to set-up an axis without doing any conditional stuff in the dimension column, so users can click the relevant sections and not see a long IF(a=1 and b=1,c,d) instead of myDimTimeOfDay.

The top axis uses a time field imported from SQL server as 'HH:MM:ss.fff tt' format.

The bottom axis uses "=Time(Frac(WAT_Collection_Time))" (Time portion of a TimeStamp )

Is there a way to force Qliksense to format the SQL imported time dimension in the load script to display the same as the bottom one? I'd rather the user didn't have to see the whole Time(Frac( stuff when selecting etc..

Re: Time as Axis, formatted AM/PM , but with granularity of milliseconds.

andoryuu — Wed, 11 Dec 2019 15:25:52 GMT

Time() and Frac() are both functions for script and chart. If you want it as a separate field why not format it as a separately named field in your data load script (or format it as a separate field in the SELECT statement from SQL for that matter)?

Re: Time as Axis, formatted AM/PM , but with granularity of milliseconds.

johnnymartinez — Wed, 11 Dec 2019 20:11:35 GMT

Hi @andoryuu - thanks for responding.

That's ( in a more concise way) my original question.

I would expect the SQL version and the QLIK manipulated version to be displayed the same - do I need to apply additional tags/mapping in the load?

I am using the load script rather than the loading wizard, which automatically picks out date fields etc..

Re: Time as Axis, formatted AM/PM , but with granularity of milliseconds.

andoryuu — Wed, 11 Dec 2019 22:07:04 GMT

It's still going to treat them as individual dates unless you use some floor() or ceil() functions. Can you post your data load script and code your using to construct your field in the viz? If you can post the whole app that would be best, but I know that's not always possible.

Re: Time as Axis, formatted AM/PM , but with granularity of milliseconds.

johnnymartinez — Fri, 13 Dec 2019 10:50:34 GMT

Hi @andoryuu - thanks again for taking the time to reply again.

The code has a lot of irrelevant stuff to this question so maybe won't be so helpful, however I took your advice and applied the Frac/Time functions in lieu of loading the SQL formatted time columns.

The below effectively swaps the original content for the same data again, but within the load script instead of in the SQL view.

IF(IS_TODAY='Y',Time(Frac(WAT_Collection_Time))) AS WAT_TSC_PERSON_TODAY,
Time(Frac(WAT_Collection_Time)) AS WAT_TSC_PERSON_HISTORY

Originally as output by SQL - which was the 'time' portion of a SQL DateTime field conditionally loaded or 'null' :

Then just the same logic again, this time performed within the load script from the original DateTime field again.

This now satisfactorily produces the correct Axis formatting and behaviour, with the caveat that it still forces military time format and NOT Am/Pm which was what'd be preferable, for clarity/ quicker reading of the chart.

Re: Time as Axis, formatted AM/PM , but with granularity of milliseconds.

johnnymartinez — Fri, 13 Dec 2019 15:03:56 GMT

<<Can't delete>><

See last post

Re: Time as Axis, formatted AM/PM , but with granularity of milliseconds.

andoryuu — Fri, 13 Dec 2019 14:54:46 GMT

@johnnymartinez You should absolutely be able to display with am pm. You probably just have to wrap it in a time(time#()) nest. I've attached a sample that accurately captures this and renders AM PM (note that the input has it in caps, but the presentation in the visualization is lower case - it detects and displays the AM/PM). If you post your QVF I can take a look. Here's an example (this is in the qvf):

timetable:
LOAD Time(Time#(TimeList,'hh:mm tt'),'hh:mm tt') as TimeList inline [
TimeList
09:00 AM
09:15 AM
09:30 AM
11:25 AM
01:00 PM
03:30 PM
];