topic Date and Datetime query in QlikView

Date and Datetime query

Patreson31 — Sat, 16 Nov 2024 19:44:07 GMT

Hi folks,

I'm having real trouble with some csv input data, tried a variety of approaches convert the fields 'Datestamp' to 'DD/MM/YYYY' and 'Period Ending' to 'DD/MM/YYYY hh:mm:ss' fields in the script to then save as a qvd - the results are pretty wacky and despite reading a ruck of forums I can't seem to get the desired output.

The incoming data is as a datestamp for both fields as follows:

The scripting I've attempted is as follows:

And the output is... well messy:

Any suggestions would be really appreciated!

Thanks in advance!

Re: Date and Datetime query

Arthur_Fong — Thu, 07 Nov 2019 01:25:56 GMT

Try this:

LEFT(DATESTAMP,INDEX(DATESTAMP,' ')) AS DATESTAMP,

TIMESTAMP#([PERIOD ENDING],'DD/MM/YYYY hh:mm')AS [PERIOD ENDING]

Re: Date and Datetime query

Patreson31 — Thu, 07 Nov 2019 11:31:36 GMT

Hi there Arthur,

Thanks for that, we're 96% of the way there! Unfortunately midnight is still returning not as intended - any thoughts?

Re: Date and Datetime query

Arthur_Fong — Thu, 07 Nov 2019 14:47:00 GMT

Try this:

if(len(DATESTAMP)=5,

date(num#(text(DATESTAMP)),'DD/MM/YYYY'),

LEFT(DATESTAMP,INDEX(DATESTAMP,' '))) AS DATESTAMP,
IF(len([PERIOD ENDING])=10,TIMESTAMP#(TEXT([PERIOD ENDING])&' 00:00','DD/MM/YYYY hh:mm')
,TIMESTAMP#([PERIOD ENDING],'DD/MM/YYYY hh:mm'))AS [PERIOD ENDING]