topic Re: Flooring mixed timestamp and date data in QlikView

Flooring mixed timestamp and date data

mwscott1 — Wed, 25 Nov 2020 16:16:04 GMT

In my data I have a mix of timestamps and dates. When I floor this to try and return all entries at dates. It appears only the entries that are dates in the original data are returned. None of the timestamp entries show.

date(floor([Completed Date]),'MM/DD/YYYY') as [Completed Date]

Re: Flooring mixed timestamp and date data

sunny_talwar — Mon, 05 Nov 2018 15:51:30 GMT

What is your TimeStamp data format? Make sure that this format is the same format specified within the environmental variable.

Re: Flooring mixed timestamp and date data

mwscott1 — Mon, 05 Nov 2018 16:40:48 GMT

mm/dd/yyyy hh:mm

Re: Flooring mixed timestamp and date data

sunny_talwar — Mon, 05 Nov 2018 16:41:40 GMT

Is that the format from environmental variables or is that how the timestamp come in as?

Re: Flooring mixed timestamp and date data

mwscott1 — Mon, 05 Nov 2018 16:49:19 GMT

this is how the timestamp shows on the .csv file

Re: Flooring mixed timestamp and date data

sunny_talwar — Mon, 05 Nov 2018 17:02:50 GMT

And what is your environmental variable set as?

Re: Flooring mixed timestamp and date data

mwscott1 — Mon, 05 Nov 2018 20:13:10 GMT

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Re: Flooring mixed timestamp and date data

sunny_talwar — Mon, 05 Nov 2018 20:33:24 GMT

Try to change this

SET TimestampFormat='M/D/YYYY h:mm';

Re: Flooring mixed timestamp and date data

mwscott1 — Mon, 05 Nov 2018 21:51:56 GMT

I tried changing that and it still only returns the dates that show in the data as a date. All timestamps are not shown. Just to be clear the original data has both dates and timestamps in the same field.

Re: Flooring mixed timestamp and date data

sunny_talwar — Tue, 06 Nov 2018 00:52:01 GMT

That make sense, would you be able to share a screenshot of a filter object of the field which you load without performing any transformation to....

Re: Flooring mixed timestamp and date data

Anil_Babu_Samineni — Tue, 06 Nov 2018 04:19:32 GMT

How about this? Likewise, You need to define

date(Alt(floor([Completed Date]), Date#([Completed Date], 'mm/dd/yyyy hh:mm'),'MM/DD/YYYY') as [Completed Date]