topic Re: Time Conversion in QlikView

Time Conversion

Sun, 28 Sep 2014 13:46:36 GMT

One of the fields in my data file is Day of week and time as follows. I am trying to load them as two fields, Day of week and Time.

Mon 3:22PM

Tue 11:05AM

Mon 4:11AM

I use Left(...) for DOW, Right(...) for the Time but can't get it to show as hh:mm

left(@3,3) as dow,

Date(mid(@3,5,7),'hh:mm') as AirTime

I have tried Time# as well.

Thanks

Re: Time Conversion

ToniKautto — Sun, 28 Sep 2014 14:20:03 GMT

For times and date it is always recommended that you use the format functions so that QlikView can properly format your data.

Left() can do the trick, but I used Subfield() instead to find the different words based on the white space.

SubField(DayTime, ' ', 1) returns the day name.

For the time part, you can use he Time#() function. This takes a text as input, and a format code that dictates how the text should be interpreted. In this case the time appears to be minutes (mm), hours (hh) and AM/PM (tt). See "Format Codes for Interpretation and Formatting Functions" in documentation of help for more format code details.

The Time#() return a dual value with the correct underlying numerical representation of the time, but the visual value still looks as the loaded text. Best practice is to reformat the presentation value also, and for this reason the outer Time() is added. After the visual value will be inline with the TimeFormat system variable.

SET TimeFormat='hh:mm tt';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LOAD

SubField(DayTime, ' ', 1) AS Day,

Time(Time#(SubField(DayTime, ' ', 2), 'hh:mmtt')) AS TimeValue

Inline [

DayTime

Mon 3:22PM

Tue 11:05AM

Mon 4:11AM

];

Re: Time Conversion

Sun, 28 Sep 2014 14:39:15 GMT

Perfect, Thank you SO MUCH!!

Re: Time Conversion

MarcoWedel — Sun, 28 Sep 2014 15:38:01 GMT

Hi,

try also to generate a 'real' weekday name, i.e. one with underlying numerical value, in order to be able to calculate with and to sort by this field:

LOAD Dual(Left(@3,3),(Index(Upper('$(DayNames)'),Upper(Left(@3,3)))+3)/4) as dow,
    Time#(Right(@3,Len(@3)-4),'hh:mmTT') as AirTime
    Inline [
@3
Mon 3:22PM
Tue 11:05AM
Thu 4:11AM
Sat 4:12AM
Mon 10:13AM
Sun 4:11PM
Wed 5:11AM
Mon 8:11PM
Fri 7:11AM
];

hope this helps

regards

Marco

Re: Time Conversion

anbu1984 — Sun, 28 Sep 2014 15:39:15 GMT

Load Left(Txt,3),Time(Time#(Mid(Txt,5,11),'hh:mmTT')) Inline [

Txt

Mon 3:22PM

Tue 11:05AM

Mon 4:11AM ];

Re: Re: Time Conversion

ToniKautto — Sun, 28 Sep 2014 16:48:26 GMT

I agree with Marco Wedel, that the day should be stored as a proper dual value.

My suggestion would be to consider avoiding string functions, as these slow down the reload if you have many of iterations. And I find it harder to read than the alternative methods.

In my additional example below preceding load is used to generate a temporary day value which I discard in the last load. This way this operation only has to be written once and the TmpDay field can be used instead. I find this easier to read and maintain.

The dual day value I generate in two different ways. Wildmatch() is nice due to it being case insensitive, so you do not have to use Upper() or Lower() to compensate for your value. Day1 will index the days from 0-6 (Mon-Sun), and Day2 is index 1-7.

If you want the AM and PM to be in upper case, just switch the tt into TT in the format variable.

SET TimeFormat='hh:mm tt';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LOAD
  TimeValue,
  Weekday(Date(WildMatch(TmpDay, 'Mon','Tue','Wed','Thu','Fri','Sat','Sun')+365, 'WWW') ) AS Day1, 
  Dual(TmpDay, WildMatch(TmpDay, 'Mon','Tue','Wed','Thu','Fri','Sat','Sun')) AS Day2 
;
LOAD
  SubField(DayTime, ' ', 1) AS TmpDay,
  Time(Time#(SubField(DayTime, ' ', 2), 'hh:mmtt')) AS TimeValue
Inline [
DayTime
Mon 3:22PM
Tue 11:05AM
Sun 4:11AM
];

Re: Re: Time Conversion

ToniKautto — Sun, 28 Sep 2014 16:50:15 GMT

Please don't forget to mark replies as helpful and/or correct, so that community members know when the question has been answered and does not required further attention.

Re: Re: Re: Time Conversion

MarcoWedel — Sun, 28 Sep 2014 22:18:01 GMT

Hi,

your solution like mine needs to be modified to adapt to localized DayNames.

To circumvent this limitation I suggest the following script (though it is case sensitive again):

LOAD WeekDay(SubStringCount(SubField('$(DayNames)',SubField(@3,' ',1),1),';')+2) as dow,
     Time#(SubField(@3,' ',2),'hh:mmTT') as AirTime
     Inline [
@3
Mon 3:22PM
Tue 11:05AM
Thu 4:11AM
Sat 4:12AM
Mon 10:13AM
Sun 4:11PM
Wed 5:11AM
Mon 8:11PM
Fri 7:11AM
];

By this already defined day names do not have to be redefined in this day of week calculation.

However, I like your Wildmatch approach for it's case insensitivity.

I tested it and found that

Weekday(Date(WildMatch(TmpDay, 'Mon','Tue','Wed','Thu','Fri','Sat','Sun')+365, 'WWW') ) AS Day1

can be abbreviated to this version:

Weekday(WildMatch(TmpDay, 'Mon','Tue','Wed','Thu','Fri','Sat','Sun')+1) as Day1

The Weekday function works modulo 7, so +365 is equal to +1.

The Date function again can be removed because as a formatting function it does not influence the numerical value of it's parameter.

An explanation for the offset of +1 leading to correct day names is that e.g. 2 is interpreted as 2 days after the starting day of the QV internal date representation, i.e. 2 days after Sat. 12/30/1899 which is Mon. 01/01/1900.

hope this helps

regards

Marco

Re: Time Conversion

Tue, 30 Sep 2014 23:36:34 GMT

Thank you so much, extremely helpful. Everything working perfectly now.

Re: Time Conversion

Tue, 30 Sep 2014 23:38:22 GMT

Thank you Anbu.

Re: Time Conversion

MarcoWedel — Wed, 01 Oct 2014 03:11:51 GMT

Please close this thread by marking correct (and helpful if you like) answers.

Thanks

Regards

Marco