Rename Hours (IF Statement)

[MalcolmCICWF]

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_),

[#2]

Hi,

Try this in edit script

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

Regards,

Venkat

[Gysbert_Wassenaar · Accepted solution]

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

[MalcolmCICWF]

This doesnt seem to be working, any other guesses?

[MayilVahanan]

Hi

Can you provide some example for hour field?

Try simply hour(fieldname,'hh tt')

ex:

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

[Gysbert_Wassenaar]

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')

[MalcolmCICWF]

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

[MayilVahanan]

HI

Try with this

hour(CC_CALL_START_TIME,'hh tt') as hour

[CELAMBARASAN]

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

[MalcolmCICWF]

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