topic Re: How to create time from 3 fields? in QlikView

How to create time from 3 fields?

patbuchanan — Thu, 30 Oct 2014 13:42:32 GMT

I've got three fields that I'm trying to combine and make one hour field if someone has the best way for this to work.

These are my three fields:

StartTimeHour = 7
StartTimeMinute = 0
StartTimeAM_PM = PM

I'm trying to get it to display 7:00 PM.

Re: How to create time from 3 fields?

Thu, 30 Oct 2014 13:45:16 GMT

Hi,

use the below

Time#(MakeTime('7','00'),'hh:mm:ss')

Re: How to create time from 3 fields?

jpenuliar — Thu, 30 Oct 2014 13:45:24 GMT

try Maketime()

Re: How to create time from 3 fields?

JonnyPoole — Thu, 30 Oct 2014 13:48:03 GMT

= MakeTime( StartTimeHour + if(StartTimeAM_PM='PM',12,0) , StartTimeMinute)

Re: How to create time from 3 fields?

Thu, 30 Oct 2014 13:51:01 GMT

Hi,

StartTimeHour &':'&StartTimeMinute &' '&StartTimeAM_PM as Time

(OR)

MakeTime( StartTimeHour + if(StartTimeAM_PM='PM',12,0) , StartTimeMinute)

Re: How to create time from 3 fields?

its_anandrjs — Thu, 30 Oct 2014 13:51:36 GMT

Use Maketime() or you can individually use fields

Load

Hour(StartTimeHour)&':'&Minute(StartTimeMinute)&' '&StartTimeAM_PM as HourTime

From Location;

Load

Maketime( Hour(StartTimeHour), Minute(StartTimeMinute) )&''&StartTimeAM_PM as HourTime

From Location;

Regards

Anand

Re: How to create time from 3 fields?

danieloberbilli — Thu, 30 Oct 2014 13:53:40 GMT

I agree with Shruti, but you might also change the SET Timeformat to SET TimeFormat='h:mm TT'; to avoid the seconds at the end and display 7:00 PM only

Re: How to create time from 3 fields?

JonnyPoole — Thu, 30 Oct 2014 13:59:05 GMT

Alternative from above that drops the if() but still reading PM vs AM

= time(time#( StartTimeHour & ':' & StartTimeMinute & ' ' & StartTimeAM_PM,'hh:mm TTT'))

= MakeTime( StartTimeHour + if(StartTimeAM_PM='PM',12,0) , StartTimeMinute)

Re: How to create time from 3 fields?

its_anandrjs — Thu, 30 Oct 2014 14:17:33 GMT

If you have and sample data file then provide or please see this small example

Tab:

LOAD * Inline

[ StartTimeHour,StartTimeMinute,StartTimeAM_PM

7,0,PM

8,15,PM

1,15,AM ];

New:

Load

Hour(time#(StartTimeHour,'hh')) as Hour,

Minute(time#(StartTimeMinute,'mm')) as Minute,

Hour(time#(StartTimeHour,'hh'))&':'&Minute(time#(StartTimeMinute,'mm'))&' '&StartTimeAM_PM as HourTime

Resident Tab;

Regards

Anand

Re: How to create time from 3 fields?

Thu, 28 Jan 2016 13:33:15 GMT

Hi Pat Buchanan,

Try this also............

Timeconvert:

LOAD

time(time#(PurgeChar(MakeTime(StartTimeHour,StartTimeMinute),'AMP')&StartTimeAM_PM,'hh:mm:ss TT'),'hh:mm TT') as merge

;

LOAD * Inline [

StartTimeHour,StartTimeMinute,StartTimeAM_PM

7,0,PM

8,30,AM

6,04,AM

5,01,PM

12,04,PM

];

Please mark this as Answered if you have found solution.

Regards,

Lokeshwari