Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

datanibbler
Esteemed Contributor

Limit a database table by a timestamp for the last booking?


Hi,

I have a table from the database with a nr. of transactions. One of those was a check of the former - someone goes and checks if all the boxes (well, a selection, anyway) are in the exact place that the database says they are. If not, he corrects that, so he does another booking.

The booking by this person is recorded along with an exact timestamp in the format >> #####,######## << - the date and time with more decimals than I need.

Now I would like to filter the table I have to make sure that was the very last booking and I get none effected after the check - so I join the timestamp of the check into my table of bookings (via the unique ID of every box) and I go like

>> WHERE [timestamp_booking] <= [timestamp_check] <<

For some reason, that doesn't work - I still get a booking that was effected a few hours after the check. I tried using the num() function or replacing the ',' with a '.', but to no avail up to now.

Can anyone help me here?

Thanks a lot!

Best regards,

DataNibbler

1 Solution

Accepted Solutions

Re: Limit a database table by a timestamp for the last booking?

Hi DataNibbler,

it could be that one of the timestamps won't be interpreted as numeric value and needed something like num#() or timestamp#(). To compare these timestamps it could be helpful to use a rounding-function like floor(timestamp, 0.000001) or similar.

- Marcus

3 Replies

Re: Limit a database table by a timestamp for the last booking?

Hi DataNibbler,

it could be that one of the timestamps won't be interpreted as numeric value and needed something like num#() or timestamp#(). To compare these timestamps it could be helpful to use a rounding-function like floor(timestamp, 0.000001) or similar.

- Marcus

datanibbler
Esteemed Contributor

Re: Limit a database table by a timestamp for the last booking?

Hi Marcus,

both are actually the same timestamp - only I have joined one of them from another table (where I had limited for the person doing the checks).

I am just experimenting with those recognition_functions. Will keep you posted.

Thanks a lot!

P.S.: It works just fine on the GUI, I can compare the two all right - so I have no idea yet what's wrong in the script.

datanibbler
Esteemed Contributor

Re: Limit a database table by a timestamp for the last booking?

Ah, I made it.

There was a simple joining error in front of that. So the format might not even have been the actual problem ...

Thanks a lot for helping anyway, Marcus!

Best regards,

DataNibbler

Community Browser