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

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

datanibbler — Mon, 23 Mar 2015 13:33:48 GMT

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

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

marcus_sommer — Mon, 23 Mar 2015 13:41:18 GMT

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

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

datanibbler — Mon, 23 Mar 2015 13:46:41 GMT

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.

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

datanibbler — Mon, 23 Mar 2015 13:50:44 GMT

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