topic Re: Rename Hours (IF Statement) in QlikView

Rename Hours (IF Statement)

MalcolmCICWF — Tue, 04 Dec 2012 15:30:22 GMT

I am trying to rename military time into standard for a report I am creating in order for people to read/understand it easier and so it is selectable. I am trying the below code but it tells me no such field...even though I am trying to rename. Qlikview does not want to let me rename as a number and letters (7AM), so I am renaming to (_7_AM_), because the code highlights correctly in this format. Does anyone have another way I can accomplish this? HOUR is the field.

IF(HOUR=7,_7_AM_),

IF(HOUR=8,_8_AM_),

IF(HOUR=9,_9_AM_),

IF(HOUR=10,_10_AM_),

IF(HOUR=11,_11_AM_),

IF(HOUR=12,_12_PM_),

IF(HOUR=13,_1_PM_),

IF(HOUR=14,_2_PM_),

IF(HOUR=15,_3_PM_),

IF(HOUR=16,_4_PM_),

IF(HOUR=17,_5_PM_),

IF(HOUR=18,_6_PM_),

IF(HOUR=19,_7_PM_),

IF(HOUR=20,_8_PM_),

Re: Rename Hours (IF Statement)

Tue, 04 Dec 2012 15:42:08 GMT

Hi,

Try this in edit script

Timestamp(Timestamp#(Hour,'hh'),'hh tt') as F1

Regards,

Venkat

Re: Rename Hours (IF Statement)

Gysbert_Wassenaar — Tue, 04 Dec 2012 15:42:09 GMT

Try time(maketime(HOUR),'hh TT')

Re: Rename Hours (IF Statement)

MalcolmCICWF — Tue, 04 Dec 2012 17:09:37 GMT

This doesnt seem to be working, any other guesses?

Re: Rename Hours (IF Statement)

MayilVahanan — Tue, 04 Dec 2012 17:13:52 GMT

Can you provide some example for hour field?

Try simply hour(fieldname,'hh tt')

ex:

=time('24:34:23','hh tt') gives 12 am

Re: Rename Hours (IF Statement)

Gysbert_Wassenaar — Tue, 04 Dec 2012 17:17:25 GMT

Then HOUR isn't a number. If it's a time then time(HOUR,'hh TT') should be enough. If it's text then first make it a number time(maketime(num#(HOUR)),'hh TT')

Re: Rename Hours (IF Statement)

MalcolmCICWF — Tue, 04 Dec 2012 17:30:07 GMT

Right now HOUR is being created from this field- hour(CC_CALL_START_TIME) AS HOUR. But I am getting the hours in military time right now. So technically I need the above field to output the hour in standard time and AM or PM stamp

Re: Rename Hours (IF Statement)

MayilVahanan — Tue, 04 Dec 2012 17:33:55 GMT

Try with this

hour(CC_CALL_START_TIME,'hh tt') as hour

Re: Rename Hours (IF Statement)

CELAMBARASAN — Tue, 04 Dec 2012 17:41:45 GMT

Hi,

Military time means in the format like

01:00 AM = 0100 right?

Format: hhmm

Try with this

Time(Time#(CC_CALLSTART,'hhmm'),'_h_TT')

Hope it helps

Re: Rename Hours (IF Statement)

MalcolmCICWF — Tue, 04 Dec 2012 17:41:52 GMT

This is what I am getting. It is not correct syntax.