Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

datanibbler
Esteemed Contributor

Abusing QlikView to historize a database table

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

13 Replies

Re: Abusing QlikView to historize a database table

HI

Can you post a script here..

Its useful for analysis the cause of issues.

MVP
MVP

Re: Abusing QlikView to historize a database table

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
Esteemed Contributor

Re: Abusing QlikView to historize a database table

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
Esteemed Contributor

Re: Abusing QlikView to historize a database table

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:MMSmiley FrustratedS), 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

MVP
MVP

Re: Abusing QlikView to historize a database table

This doesn't makes much sense. If ACTIVATION_DATE_DIFF is a time formatted with HH:MMSmiley FrustratedS 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
Esteemed Contributor

Re: Abusing QlikView to historize a database table

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:MMSmiley FrustratedS' next.

MVP
MVP

Re: Abusing QlikView to historize a database table

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

datanibbler
Esteemed Contributor

Re: Abusing QlikView to historize a database table

Hi Ralf,

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

MVP
MVP

Re: Abusing QlikView to historize a database table

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

Community Browser