Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Patreson31
Contributor II
Contributor II

Date and Datetime query

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:

Inputdata.JPG

The scripting I've attempted is as follows:

Scripting.JPG

And the output is... well messy:

OutputData.JPG

Any suggestions would be really appreciated!

 

Thanks in advance!

Labels (1)
1 Solution

Accepted Solutions
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

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]

View solution in original post

3 Replies
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Try this:

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

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

Patreson31
Contributor II
Contributor II
Author

Hi there Arthur,

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

Output.JPG

 

 

 

 

Arthur_Fong
Partner - Specialist III
Partner - Specialist III

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]