- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
The scripting I've attempted is as follows:
And the output is... well messy:
Any suggestions would be really appreciated!
Thanks in advance!
- Tags:
- qlikview_scripting
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this:
LEFT(DATESTAMP,INDEX(DATESTAMP,' ')) AS DATESTAMP,
TIMESTAMP#([PERIOD ENDING],'DD/MM/YYYY hh:mm')AS [PERIOD ENDING]
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi there Arthur,
Thanks for that, we're 96% of the way there! Unfortunately midnight is still returning not as intended - any thoughts?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]