topic Re: Date formatting in QlikView

Date formatting

Fri, 29 Jan 2016 16:37:42 GMT

I have a few issues from the picture in the attachment.

First, creation_date in the top table is being loaded in a very odd format.

Second, I can create list boxes of creation_dt and change the number format. I made one with 'timestamp' number format and one with 'date' format. I am seeing duplicate date records, due to the timestamps.

I need help please 1. reformatting the creation_dt field and 2. having the dates only load once. I assume these go hand in hand.

Thank you

Re: Date formatting

sunny_talwar — Fri, 29 Jan 2016 17:34:22 GMT

Try these while loading

LOAD TimeStamp(creation_date) as creation_timestamp,

Date(Floor(creation_date)) as creation_date

FROM Source

Re: Date formatting

Anonymous — Fri, 29 Jan 2016 17:36:48 GMT

Date(floor(yourdate))

The floor() will truncate the seconds off the underlying numeric value, leaving just the pure date.

Re: Date formatting

Colin-Albert — Fri, 29 Jan 2016 17:41:23 GMT

Your listbox is showing multiple dates because you have changed the displayed value of the date to hide the time portion of the datetime value but the actual data values still include the time so a value is displayed for each timestamp.

As suggested by Sunny and Bill, use floor() to change the number to an integer, then the date will be correct and only show once is your listbox.