I am facing a challenge very similar to one I have already solved:
- A new plant has just taken up production a short while ago.
- I have to build a history of one database table, just like I did in this plant, because in the database it has no history.
- To do that, I have a LOAD statement with just two fields:
- The current date
- A count of item_numbers with several WHERE clauses.
- The result of this LOAD would then be stored as a qvd file.
<=> With all the WHERE clauses in place, this load returns 0 records instead of returning the number 0 when no item_numbers were found matching the WHERE clauses. I have already tried avoiding this by way of an IF() fct, but so far it doesn't work.
When I apply all the filters to the primary qvd_LOAD, then I get only 3 records, but those do have the date_field (timestamp, actually) all right, only the diff_field is still interpreted as a time with 'AM' and 'PM'.
When I use a num() fct on this, it seems to work.
I'll check.
P.S.: Oh my - now I have a numeric value and I can compare it all right - but instead of now getting 1 record, just saying 0 (because, acc. to the listboxes, none of the items is older than 5 days), I get 0 records. I have to find a way of circumventing that.
P.P.S.: It gets stranger and stranger - I can load this num() value from the qvd all right, but I cannot reference it in any way in my RESIDENT LOAD - when I insert it into that load, I get an "Invalid expression" error...
so, for some reason, this field seems sensitive to the touch - I cannot include it in my RESIDENT LOAD to check, if I do I get an "Invalid expression" error. But I know the value, so that is not too bad.
(in this instance, the value is 3)
When I just include it in a WHERE expression, however, it works fine if I say WHERE Days_on_area > 1 - I still get that one record then.
When I change it to WHERE Days_on_area>5, however - it should be 5 - I get 0 records. OK, there is no record matching that condition, but I would like to avoid that by returning a 0 value in that case.
So, even though the thing stays mysterious, it is now down to quite a "simple" problem. I have tried an "<>0" condition and an ISNULL() condition, but those did not yet do the trick.
It might be because of QlikView's way of executing LOAD statements top-down - the COUNT I have, in itself, is okay, so the functions I have tried did not budge - only the WHERE clause reduces the nr. of records found to 0.
Thus I have to do like a RESIDENT LOAD. The problem is that I have nothing in that historical qvd yet, I was just making a start on it...
P.S.: I would have to find a way to check the nr. of records that was written to that qvd file by the preceding LOAD statement.
Can that be done?
Then I could find out if any records were found and in the opposite case, I would just construct a record with only today's date and a 0.
