topic Re: Qlik Sense in App Development

Qlik Sense

nigelapt — Tue, 13 Aug 2024 22:23:44 GMT

Hi folks

I am having difficulty getting QlikSense to recognize a timestamp as a valid timestamp.

I am loading performance data from SQL to a temporary table:

SET TimestampFormat='YYYY-MM-DD hh:mm:ss.ffffff';

LIB CONNECT TO '01-PRD-SQLSERVER';

PERFDATA_Temp:

SQL SELECT

Perf.vPerfHourly.DateTime AS [08 Sample Date/Time],

FROM
dbo.vPerformanceRule WITH (NOLOCK)

Disconnect;

The data loads as follows to the temp table eg:

I then want to extract the timestamp and date etc from the temp data and write to a new table.

PERFDATA:

Load

Timestamp(Timestamp#([08 Sample Date/Time], 'YYYY-MM-DD hh:mm:ss.ffffff'), 'YYYY-MM-DD hh:mm') as [Sample Date/Time],
Date(Timestamp#([08 Sample Date/Time], 'YYYY-MM-DD hh:mm:ss.ffffff'), 'YYYY-MM-DD') as [Sample Date]

Resident PERFDATA_Temp;

Drop Table PERFDATA_Temp;

However the function fails and this is what is returned:

I am not sure what I am doing wrong.

Any advice would be greatly appreciated.

Thanks

Nigel

Re: Qlik Sense

poklegoguy — Wed, 14 Aug 2024 01:17:09 GMT

Can't really find any fault in your script, I used the identical script as yours but mine worked.

Re: Qlik Sense

anat — Wed, 14 Aug 2024 02:21:59 GMT

Can you try using floor function

Date(floor (field)) as date,

Timestamp (floor(field)) as datetime

Re: Qlik Sense

Kushal_Chawda — Wed, 14 Aug 2024 09:22:34 GMT

@nigelapt tty to trim the spaces

PERFDATA_Temp:

LOAD trim([08 Sample Date/Time]) as [08 Sample Date/Time];

SQL SELECT

Perf.vPerfHourly.DateTime AS [08 Sample Date/Time],

FROM
dbo.vPerformanceRule WITH (NOLOCK)

Re: Qlik Sense

nigelapt — Wed, 21 Aug 2024 11:42:15 GMT

Many thanks Kushal! That sorted it!