Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Round off Time in Timestamp to Hours

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

SS.PNG

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?

7 Replies
Highlighted

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

Highlighted
Creator II
Creator II

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.

Highlighted

sorry, wrong format.

Try

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


instead.



Marco

Highlighted
Creator II
Creator II

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?

Highlighted

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

Highlighted
Creator II
Creator II

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.

Highlighted

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