Abusing QlikView to historize a database table

[datanibbler]

Hi,

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.

Can anybody help me here?

Thanks a lot!

Best regards,

DataNibbler

[datanibbler]

Hi Ralf,

that's what I was assuming all the time.

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...

[datanibbler]

Hi Ralf,

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.

[rbecher]

I assume I cannot solve this without having sample data and script..

[datanibbler]

Hi,

I think now I have finally made it.

The trouble is, I will only know tomorrow if it really works.