Record not found - is something wrong with my filter?

[datanibbler]

Hi,

in a new report I have built, I was informed by one of the users that one combination of data was not found by the script although the event concerned (a receiving process) was in June of this year. It's already archived, but I am concatenating the archive (starting from January of last year) and the current transactional table, so it should be.

Well, I tested and indeed I could not find it in my selfmade "archive+current" table. Just now I tried loading the entire archive of that table without any restriction and then I tried to reconstruct the filter on the GUI - and guess what, I found the event I was looking for.

=> The only logical explanation I can come up with is that there's something wrong with my filter - but I did find events from December of last year.

Maybe someone here can help me see clear on this. My filtering is as follows:

LOAD

       ...

WHERE DATE(FLOOR(num(TRAN_DATE))) >= Yearstart(YearStart(Today())-1)

(TRAN_DATE is a timestamp and it is a STRING to begin with, which is why I need the num())

Thanks a lot!

Best regards,

DataNibbler

[sunny_talwar · Accepted solution]

Not entirely sure I understand, but one thing I would say is to simplify your Where statement here:

Where Floor(TRAN_DATE) >= YearStart(Today(), -1);

[Gysbert_Wassenaar]

If it's really a string then you need to use the Date# or Timestamp# function to turn it into a real timestamp. Check what the result of num(TRANDATE)+1 is. If TRANDATE is a real timestamp and thus a number than adding 1 will work, if it's a string it won't.

[datanibbler]

Hi,

thanks for the answers! But the thing is more difficult than this 😉

My filter, unnecessarily complicated as it might be, seems to not be the issue: I have now loaded the data once again with the filter in place - just as it was - and again, I find one record from December of last year and every day of this year - I even find events on the day I am looking for - only that specific record is missing.

I'll try to explain the process behind it:

- What I am generally looking for in this step of the script is receiving_bookings (when we receive goods, a booking to the database takes place which has the code SPRECEIVE, a timestamp and an item_number.

- This archiving_logic is built into our primary data_loading script (one step before the report I have built, so to speak)

- There is no filter on item_numbers, only on the date - start of last year like I said.

- As I said, I can find the day that that particular SPRECEIVE-booking took place - what I cannot find is that particular item_number.

<=> However, there is only one filter, so logically that is the only point_of_failure in the logic, so I will start by simplifying my filter - that Date-function was there before (in a LOAD of the current table which was there before I started on this), so I will not mess with that for a start - only it could be that the comparison-thingy of my filter does not match the outcome ... Let's see.

[Gysbert_Wassenaar]

Perhaps the TRAN_DATE value for that particular record is not a proper timestamp or in a different format that the values of the other records.

[datanibbler]

Hmmm ... whatever that was - it works now 😉

I guess your hint was the right one, Sunny!

Thanks a lot!

Best regards,

DataNibbler