Search code examples
stringtimestampintegerqliksense

Simple way to convert time text strings to ints for numeric operation in Qliksense data load editor

TimeTemp displays as such 168@06:43:23AM

stationTime displays opening and closing times of service area stations as such 6AM-10AM

I’ve separated TimeTemp into 2 different columns, initialTemp and initialTempTime

I’ve separated stationTime into 2 different columns, openingTime and closingTime

The desired conditional statement is "if not within every 2 hour interval, turn initialTemp cell red", so the logic is as follows:

if(initialTempTime <= openingTime + 2, green(), red())

which should turn initialTemp as green if the expression <= is true, else red. However, this has been turning the initialTemp cells all into red.

This made me suspect that the expression is not being evaluated correctly and just defaulting to the else . I’m confident this is the case as initialTempTime , openingTime have been confirmed to be of text string data types.

Since a string + int cannot be evaluated to a truthy, it defaults to red() thus turning all the initialTemp cells into red.

The solution would be to typecast the strings into int, so that openingTime of say 6AM can get added 2 hours and the numeric operation can be properly evaluated.

What is a simple way to convert time text strings to ints for conditional numeric operations?

Attempts thus far:

Time#(SubField(stationTime,'-',1),'hh:mm:ss') as openingTimeTest which seems to turn all openingTime fields as - (null)

Num(Sum(SubField(stationTime,'-',1) + tempRecordingInterval)) as openingTimeRecordingInterval where SET tempRecordingInterval=2;

Keepchar([initialTempTime],'0123456789.') as [initialTempTimeNum]

Solution

  • Consider the script below. (ive split it in multiple steps/loads just for clarity)

    RawData:
Load 
  // convert to time
  Time(Time#( openingTime, 'h:mm:ss')) as openingTime,
  Time(Time#( closingTime, 'h:mm:ss')) as closingTime
;
Load
  // "convert" to time looking string 
  // 6AM will be 6:00:00
  // 6PM will be 18:00:00
  if( index(openingTime, 'AM') > 0, KeepChar(openingTime, '01234567890'),
      KeepChar(openingTime, '01234567890') + 12) & ':00:00' as openingTime,
      
  if( index(closingTime, 'AM') > 0, KeepChar(closingTime, '01234567890'),
      KeepChar(closingTime, '01234567890') + 12) & ':00:00' as closingTime
;
Load
  // split the station time
  SubField(stationTime, '-', 1) as openingTime,
  SubField(stationTime, '-', 2) as closingTime
;
Load * Inline [
stationTime
6AM-10AM
6PM-10PM
];

    The result table will look like:

    Data viewer

    (the display is based on the value of SET TimeFormat variable in the beginning of your script)

    Once the data is in Time format then we can add/subtract hours like this:

    = time( openingTime + 2 / 24 ) // add two hours to openingTime field

    = time( openingTime - 2 / 24 ) // subtract two hours to openingTime field

    And if we select "06:00:00" value in openingTime the two hours addition will give us:

    Selection