Need help writing an expression for time extraction in Qlik

Question

I am new to Qlik and my project requires me to create a time field of format HH:MM (AM/PM) from existing time field of format HH:MM:SS (AM/PM). I am having trouble finding the expression I should write in the data manager to make it happen.

Example:

Existing time: 12:42:23 PM

Format needed: 12:42 PM

Answer 1

You can use an expression like this:

time( time#(SomeField, 'hh:mm:ss TT'), 'hh:mm TT') )

Im using time# function first to "tell" Qlik that the data in SomeField is in format hh:mm:ss TT and then wrapping the result in time function to convert/display the result as time but passing the second (optional) parameter to specify the output format. In our case hh:mm TT (without :ss) part.

(If the second parameter for time function is not provided then Qlik will use the default time format, specified in the TimeFormat variable at the beginning at the script)

Example script:

RawData:
Load
  SomeField,
  time( time#(SomeField, 'hh:mm:ss TT'), 'hh:mm TT') as TransformedField
;
Load
  '12:42:23 PM' as SomeField
AutoGenerate(1)
;

and the result: