Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
datanibbler
Esteemed Contributor

Record not found - is something wrong with my filter?

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

1 Solution

Accepted Solutions

Re: Record not found - is something wrong with my filter?

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

5 Replies

Re: Record not found - is something wrong with my filter?

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

Re: Record not found - is something wrong with my filter?

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.


talk is cheap, supply exceeds demand
datanibbler
Esteemed Contributor

Re: Record not found - is something wrong with my filter?

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.

Re: Record not found - is something wrong with my filter?

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.


talk is cheap, supply exceeds demand
datanibbler
Esteemed Contributor

Re: Record not found - is something wrong with my filter?

Hmmm ... whatever that was - it works now ;-)

I guess your hint was the right one, Sunny!

Thanks a lot!

Best regards,

DataNibbler

Community Browser