Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

String Conversion to Date, Time, Timestamp in certain format

Hi guys, I am pretty new to Qlikview. Please help with below question.

I have two string fields for date and time.

DATE_CREATED: yyyymmdd

TIME_CREATED: hhmmss

I converted date string to Date format using

Date(Date#("DATE_CREATED",'YYYYMMDD'),'MM/DD/YYYY')

I converted time string to time format using (not sure if this is correct)

Time(Time#("TIME_CREATED",'HHMMSS'),'HH:MM:SS TT')


My requirement is:


1. Convert the time above to just hours only. ie. If 11:20:02 AM has count 10 and 11:30:32 AM has count 20, I want both to be 11 AM and be a single row adding the calculation for that field into one i.e 30.

2. After getting the hours format above, I would like to concatenate the DATE_CREATED and TIME_CREATED into one field. For this, I have used the below but I get results with future times and dates also which is wrong.

TimeStamp(Floor(TimeStamp#(DATE_CREATED&TIME_CREATED, 'YYYYMMDDhhmmss'), 1/24), 'MM/DD/YYYY hh') as Date_Time

and

Timestamp(Date#("DATE_CREATED"&"TIME_CREATED",'YYYYMMDD HHMMSS'),'MM/DD/YYYY HH TT') AS Date_Time


3. After concatenating above in step 2, I would get the time in EST. I would like to make it Central Time by subtracting 1 hour to the result. Please let me know how to do it.


Please help guiding me in the above 3 questions.

7 Replies
Highlighted
Master
Master

This can help for your first problem

Time(Time#("TIME_CREATED",'hhmmss'),'hh') as Hour

Highlighted

What if you do something like this?

Date(Date#("DATE_CREATED",'YYYYMMDD'),'MM/DD/YYYY') &

Interval(Time#("TIME_CREATED",'HHMMSS'),'HH') as Final_Date_Format

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator II
Creator II

I found the solution to get it into MM/DD/YYYY hh:mm:ss format, but is there a way I can round it off to hours?

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


For example, 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 with the result   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 with concatenated result.

Highlighted

The Time function formats the output time, but does not alter the actual Time value held against the record.

To change the actual time value to just minutes or hours, you must use a rounding function such as round(), ceil() or floor().

For example

To round time to the nearest 30 minutes      round(TimeField, 30/(24 *60))

To round down to the nearest 30 minutes      floor(TimeField, 30/(24 *60))

To round up to the nearest 10 minutes          ceil(TimeField, 10/(24 *60))


Have a look at this post for more details The Date Function

Highlighted
Creator II
Creator II

I am not able to implement it to my timestamp. Can you please implement it to the below rounding off the hours, Colin?

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

Highlighted
Creator II
Creator II

I got it Colin. I used the round function the following way and it helped achieve the results.

TimeStamp(floor(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' ),1/24),'MM/DD/YYYY hh') as DATE_TIME_EST

How can I convert the overall output result from EST to Central time by subtracting 1 hour from above formula?

Highlighted

To remove an hour from the time value, subtract 1/24 from the value.

Subtracting 1 gives a datetime value 1 day earlier, so subtracting 1/24 is a hour earlier

DATE_TIME_CST = DATE_TIME_EST - (1/24) ;