Time Conversion

[#1]

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

[ToniKautto]

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

];

[#3]

Perfect, Thank you SO MUCH!!

[MarcoWedel]

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

[anbu1984]

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

Txt

Mon 3:22PM

Tue 11:05AM

Mon 4:11AM ];

[ToniKautto]

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

[ToniKautto]

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.

[MarcoWedel]

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

[#9]

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

[#10]

Thank you Anbu.