Unable to evaluate interval field in IF statement

[#1]

Hi,

I hope that somebody could help me solve the following problem please.

I have created a QlikView variable that evaluates an expression in order to extract the hour from a date field - having got the hour, if it is less that 7 then return 'AM', otherwise return 'PM' (the final calculation is a bit more involved than this but this demonstrates the problem).

NOTE: in the variable expression below, the field EventDate is a date time, for example, 21-Jun-2011 06:59:59.

The following variable expression correctly returns the hour element from the timestamp as a string (E.g. 21-Jun-2011 06:59:59 returns '06'):

=interval
(frac
  (timestamp
   (
    timestamp# (EventDate, 'DD-MMM-YYYY hh:mm:ss'),
    'DD/MM/YYYY hh:mm:ss'
   )
  ), 'hh'
)

However, when I add an if statement, the expression always evaluates true whatever the hour number (i.e. returns 'AM'):

=if( (interval
(frac
  (timestamp
   (
    timestamp# (EventDate, 'DD-MMM-YYYY hh:mm:ss'),
    'DD/MM/YYYY hh:mm:ss'
   )
  ), 'hh'
)
     ) < '07', 'AM', 'PM')

It seems that the problem would be solved if I convert the interval hour value to an integer but am not sure how to do this.

I have attached the QlikView document that demonstrates the problem.

Many thanks.

[#2]

Hi,

Try:

=if( num(interval

(frac

  (timestamp

   (

    timestamp# (EventDate, 'DD-MMM-YYYY hh:mm:ss'),

    'DD/MM/YYYY hh:mm:ss'

   )

  ), 'hh'

)

     ) < 7, 'AM', 'PM')

[kaushiknsolanki]

Hi,

   Have a look at the example.

   I tried to achieve in different manner.

   Hope this will help you.

Regards,

Kaushik Solanki

[#4]

Hi,

You are using single quate '7'. Instead use only 7, as you are comparing numeric value.