topic Re: String Conversion to Date, Time, Timestamp in certain format in QlikView

String Conversion to Date, Time, Timestamp in certain format

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

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.

Re: String Conversion to Date, Time, Timestamp in certain format

shiveshsingh — Thu, 14 Jun 2018 18:26:35 GMT

This can help for your first problem

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

Re: String Conversion to Date, Time, Timestamp in certain format

Anil_Babu_Samineni — Fri, 15 Jun 2018 06:05:37 GMT

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

Re: String Conversion to Date, Time, Timestamp in certain format

haneeshmarella — Fri, 15 Jun 2018 13:31:48 GMT

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.

Re: String Conversion to Date, Time, Timestamp in certain format

Colin-Albert — Fri, 15 Jun 2018 14:12:37 GMT

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

Re: String Conversion to Date, Time, Timestamp in certain format

haneeshmarella — Fri, 15 Jun 2018 15:00:54 GMT

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

Re: String Conversion to Date, Time, Timestamp in certain format

haneeshmarella — Fri, 15 Jun 2018 15:14:49 GMT

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?

Re: String Conversion to Date, Time, Timestamp in certain format

Colin-Albert — Fri, 15 Jun 2018 16:09:04 GMT

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) ;