Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
HI
Can you post a script here..
Its useful for analysis the cause of issues.
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
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
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
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.
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.
I see.. What if you use WHERE num(ACTIVATION_DATE_DIFF) > 5 then?
Hi Ralf,
no, that still does not work - as soon as I activate that WHERE clause, the script returns 0 records.
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).