topic Re: Date Conversion in QlikView

Date Conversion

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

Getting date in below format :

Explanation of date field format :- 1/1/4000 = 0 days

(1st octet is month, 2nd Octet is days and 3rd Octet is hours).

For eg: 000 8:22:13 (7 months, 21 days, 13 hrs)

This is required to be converted to standard format in minutes i.e(mm)

Re: Date Conversion

Gysbert_Wassenaar — Thu, 11 Aug 2016 07:36:22 GMT

Interval(makedate( subfield('000 8:22:13', ' ', 1) + 4000, subfield(subfield('000 8:22:13', ' ', 2), ':', 1)-1, subfield(subfield('000 8:22:13', ' ', 2), ':', 2)-1) + num#(subfield(subfield('000 8:22:13', ' ', 2), ':', 3))/1440 , 'mm')

Re: Date Conversion

Pulkit_Thukral — Thu, 11 Aug 2016 09:05:19 GMT

Thanks Gysbert,but it does not solve the problem.

When i am replacing date field with the hard-coded value in your expression,it gives me null.

Sample Data:

TOTAL_TIME

1/1/4000 6:32

1/1/4000 0:00

1/1/4000 12:12

1/1/4000 7:35

Re: Date Conversion

Gysbert_Wassenaar — Thu, 11 Aug 2016 09:17:24 GMT

Your sample data is completely different from the format 000 8:22:13 that you posted. Please give the correct definition of the format.

Re: Date Conversion

Pulkit_Thukral — Thu, 11 Aug 2016 09:24:33 GMT

The below clarification was been provided to me on asking the date format.

Explanation of date field format :- 1/1/4000 = 0 days

(1st octet is month, 2nd Octet is days and 3rd Octet is hours).

For eg: 000 8:22:13 (7 months, 21 days, 13 hrs)

Not Sure what is the difference here.

Even to me data looks very different with no value matching the example.

I will provide the required details after confirming with data provider

Re: Date Conversion

Gysbert_Wassenaar — Thu, 11 Aug 2016 09:29:04 GMT

1/1/4000 is not the same format as 000. So what date and time is 1//1/4000 6:32? And what should the end result be for that value?

Re: Date Conversion

Pulkit_Thukral — Thu, 11 Aug 2016 18:50:06 GMT

Hi Gysbert,

I got some clarity with the data.

From 1/1/4000 ,1 and 4000 has to be directly removed leaving middle section which gives days left ,which is 1 less than mentioned days.

Example:1/n/4000,

Processed data=n

lets have 1/3/4000 3:45:12 AM

It means 2 days 3 hours 45 mins 12 sec.

For getting this part ,i have used below expression:

=Timestamp#( IF(WildMatch(TOTAL_TIME,'*1/1/4000*'),date(TOTAL_TIME,'hh:mm:ss'),DATE(date(TOTAL_TIME,'d hh:mm:ss')-1,'d hh:mm:ss')) ) ,which is getting me correct data.

Now i want to add this field value to another field value.

Ex: a field named start_time has to be added with TOTAL_TIME FIELD.

Let's say start_time for a ticket is '7/13/2016 3:45:12 TT' and Total_Time field for the same is :2 days 3 hours 45 mins 12 sec.

So how can we add these 2 fields. (i.e. 7/13/2016 3:45:12 PM + 2 3:45:12) wherein 2 days 3 hours 45 min and 12 sec has been added to open_time field.

How to achieve this addition ?

Re: Date Conversion

Gysbert_Wassenaar — Fri, 12 Aug 2016 05:41:58 GMT

Add them: field1 + field2. Just make sure that one of the fields is a date or timestamp and the other is an interval of days+time.