topic Re: Round off Time in Timestamp to Hours in QlikView

Round off Time in Timestamp to Hours

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

Hi,

I have the following timestamp which contains 'Date Field' and 'Time String' that gives a result of the below picture.

TimeStamp((TimeStamp#(DATE_CREATED & IF( LEN(TIME_CREATED) = 3 , ('000' & TIME_CREATED), IF ( LEN(TIME_CREATED) =4 , '00' & TIME_CREATED,IF( LEN(TIME_CREATED) =5 , '0' & TIME_CREATED, TIME_CREATED ))),'YYYYMMDDhhmmss' )),'MM/DD/YYYY hh:mm:ss') as DATE_TIME_EST

I want to have a result such that it concatenates the hours together. Ex. 06/14/2018 00:01:08 and 06/14/2018 00:02:36 show as 06/14/2018 12 AM or 06/14/2018 00 and sums up their respective results.

Also, how do I subtract an hour from the above result to get my time in CST as the result is in EST?

Re: Round off Time in Timestamp to Hours

MarcoWedel — Thu, 14 Jun 2018 20:59:01 GMT

I guess as you already have DATE_CREATED there is no need for rounding your timestamps of fto daystart.

Timestamp(Date#(DATE_CREATED,'MM/DD/YYYY'),'MM/DD/YYYY hh TT')

should do the trick.

hope this helps

regards

Marco

Re: Round off Time in Timestamp to Hours

haneeshmarella — Thu, 14 Jun 2018 21:05:52 GMT

Hi Marco,

Your answer is not applicable to my scenario.

The date is of string format YYYYMMDD. The above timestamp function I have utilized gives me a timestamp output based on the string format input of date and time.

I need to get the hours rounded off for the timestamp output shown in the screenshot above. I also gave an example below the screenshot of what I am looking for. Hope this clears my question.

Re: Round off Time in Timestamp to Hours

MarcoWedel — Thu, 14 Jun 2018 21:35:14 GMT

sorry, wrong format.

Try

Timestamp(Date#(DATE_CREATED,'YYYYMMDD'),'MM/DD/YYYY hh TT')

instead.

Marco

Re: Round off Time in Timestamp to Hours

haneeshmarella — Thu, 14 Jun 2018 21:40:26 GMT

There is no time in DATE_CREATED.

The time comes from IF( LEN(TIME_CREATED) = 3 , ('000' & TIME_CREATED), IF ( LEN(TIME_CREATED) =4 , '00' & TIME_CREATED,IF( LEN(TIME_CREATED) =5 , '0' & TIME_CREATED, TIME_CREATED and is combined with the date string that comes from DATE_CREATED to form the time stamp format of MM/DD/YYYY hh:mm:ss. I need to convert this output to MM/DD/YYYY hh where the hours are concatenated by summing their respective rows. Makes sense?

Re: Round off Time in Timestamp to Hours

MarcoWedel — Thu, 14 Jun 2018 22:10:33 GMT

can you elaborate on the "concatenating hours by summing their rows" part?

Re: Round off Time in Timestamp to Hours

haneeshmarella — Fri, 15 Jun 2018 13:28:40 GMT

Sure, if there are two rows of timestamps:

Timestamp Count of Orders

06/15/2018 14:30:32 20

06/15/2018 14:45:02 10

Timestamp Count of Orders

So I want it to be in the format of 06/15/2018 14 30

I want to convert the above timestamp formula (in my original question) I implemented that results in MM/DD/YYYY hh:mm:ss into the format of MM/DD/YYYY hh. Hope this clarifies.

Re: Round off Time in Timestamp to Hours

Colin-Albert — Fri, 15 Jun 2018 14:51:19 GMT

Haneesh - You need to round yout timestamp to remove the minute values, then your count will be correct.

The issue is the same as my answer to your other post String Conversion to Date, Time, Timestamp in certain format