topic Re: Abusing QlikView to historize a database table in QlikView

Abusing QlikView to historize a database table

datanibbler — Mon, 02 Sep 2013 12:55:54 GMT

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

Re: Abusing QlikView to historize a database table

MayilVahanan — Mon, 02 Sep 2013 13:09:47 GMT

Can you post a script here..

Its useful for analysis the cause of issues.

Re: Abusing QlikView to historize a database table

rbecher — Mon, 02 Sep 2013 13:12:33 GMT

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

Re: Abusing QlikView to historize a database table

datanibbler — Mon, 02 Sep 2013 13:22:39 GMT

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

Re: Abusing QlikView to historize a database table

datanibbler — Mon, 02 Sep 2013 13:55:12 GMT

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

Re: Abusing QlikView to historize a database table

rbecher — Mon, 02 Sep 2013 14:01:32 GMT

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.

Re: Abusing QlikView to historize a database table

datanibbler — Mon, 02 Sep 2013 14:20:12 GMT

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.

Re: Abusing QlikView to historize a database table

rbecher — Mon, 02 Sep 2013 14:24:07 GMT

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

Re: Abusing QlikView to historize a database table

datanibbler — Mon, 02 Sep 2013 14:28:36 GMT

Hi Ralf,

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

Re: Abusing QlikView to historize a database table

rbecher — Mon, 02 Sep 2013 14:30:39 GMT

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

Re: Abusing QlikView to historize a database table

datanibbler — Mon, 02 Sep 2013 14:35:25 GMT

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

Re: Abusing QlikView to historize a database table

datanibbler — Mon, 02 Sep 2013 15:22:42 GMT

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.

Re: Abusing QlikView to historize a database table

rbecher — Mon, 02 Sep 2013 16:07:50 GMT

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

Re: Abusing QlikView to historize a database table

datanibbler — Tue, 03 Sep 2013 12:04:36 GMT

Hi,

I think now I have finally made it.

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