topic Unable to evaluate interval field in IF statement in QlikView

Unable to evaluate interval field in IF statement

Wed, 22 Jun 2011 10:01:09 GMT

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.

Unable to evaluate interval field in IF statement

Wed, 22 Jun 2011 10:21:54 GMT

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

Re: Unable to evaluate interval field in IF statement

kaushiknsolanki — Wed, 22 Jun 2011 10:39:10 GMT

Hi,

Have a look at the example.

I tried to achieve in different manner.

Hope this will help you.

Regards,

Kaushik Solanki

Unable to evaluate interval field in IF statement

Wed, 22 Jun 2011 11:46:40 GMT

Hi,

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