13 Replies Latest reply: Sep 3, 2013 8:04 AM by Friedrich Hofmann RSS

    Abusing QlikView to historize a database table

    Friedrich Hofmann

      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 ramasamy

          HI

           

          Can you post a script here..

          Its useful for analysis the cause of issues.

          • Re: Abusing QlikView to historize a database table
            Ralf Becher

            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
                Friedrich Hofmann

                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
                    Friedrich Hofmann

                    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
                        Ralf Becher

                        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
                            Friedrich Hofmann

                            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
                                Ralf Becher

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

                                  • Re: Abusing QlikView to historize a database table
                                    Friedrich Hofmann

                                    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
                                        Ralf Becher

                                        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
                                            Friedrich Hofmann

                                            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
                                                Friedrich Hofmann

                                                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.