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

[MayilVahanan]

HI

Can you post a script here..

Its useful for analysis the cause of issues.

[rbecher]

Hi Friedrich,

could you post a script example. If you make a group by current date the count should have at least one record.

- Ralf

[datanibbler]

Hi,

I learnt it has nothing to do with my script - the error lies elsewhere.

That makes it rather more difficult, but we'll find out eventually.

In case that should solve the issue, I'll close this thread.

Best regards,

DataNibbler

[datanibbler]

Hi,

it is very strange:

For some reason - although as far as we can look, everything seems to be equal in the two plants - the date_field must be somehow different in the new database. The calculated ACTIVATION_DATE_DIFF, which in the old app looked like a time (HH:MM:SS), but without any suffix, in the new app is interpreted as a time (with 'AM' and 'PM' and thus cannot be used in the way I was planning to - in the old app, there is a

       >> WHERE ACTIVATION_DATE_DIFF > 5  <<

to return all the items that have been in this place for more than five days. This won't work in the new one.

The strange thing is, I can add both fields as listboxes to the GUI in the new app and there are values, only the ACTIVATION_DATE_DIFF field is a time with 'AM' and 'PM'.

Could you lend me a hand here?

Thanks a lot!

Best regards,

DataNibbler

[rbecher]

This doesn't makes much sense. If ACTIVATION_DATE_DIFF is a time formatted with HH:MM:SS how can it be interpret as an amount of days to be compared > 5?

However, I still cannot recognize (without any script or explanation) if it's a Where against a database or QVD load.

[datanibbler]

Hi Ralf,

the WHERE clause is only in a RESIDENT LOAD based on a qvd load.

I have the same thing for the old plant and it works fine - the INTERVAL() fct returns both a STRING and a numeric value. That's how I can compare it.

I'm still onto this and I still cannot figure out why, in the new plant, QlikView interprets this INTERVAL() output as a time with 'AM' and 'PM' which it does not do with the old plant - it's also a new database, but it should be exactly the same, just a copy. All the formulas are the same, too, as far as I can tell.

Thanks a lot!

Best regards,

DataNibbler

P.S.: The INTERVAL() in that instance has no specified format at all. I'll try specifying 'D HH:MM:SS' next.

[rbecher]

I see.. What if you use WHERE num(ACTIVATION_DATE_DIFF) > 5 then?

[datanibbler]

Hi Ralf,

no, that still does not work - as soon as I activate that WHERE clause, the script returns 0 records.

[rbecher]

Hard to say what's the cause with no data .. I guess it's in the data and has nothing to do with the shown format (AM/PM).