9 Replies Latest reply: Sep 6, 2013 7:43 AM by Friedrich Hofmann RSS

    Check result of preceding LOAD statement?

    Friedrich Hofmann


      Hi,

       

      I have a problem that comes down to this:

      - I load a COUNT(item) from a table, with several WHERE clauses in the statement

      => Those WHERE clauses reduce the nr. of records found to 0

      => The LOAD statement returns 0 records

      <=> I would like, instead, to load one record with

           - today's date (I have that as a variable as it's not in the table)

           - a nr. 0 to indicate that no items matched the criteria on that day.

       

      Is there a method to check whether the preceding LOAD statement returned any records?

       

      Thanks a lot!

      Best regards,

       

      DataNibbler

       

      P.S.: Hmm - the NoOfRows() fct does this - unfortunately, I first load the qvd that is already there and then I CONCATENATE a second LOAD from the qvd - apparently, the fct. looks at the first LOAD and since that does return records, it doesn't trigger.

        • Re: Check result of preceding LOAD statement?
          Gonzalo Bianchi

          You can provide the name of the table to the function so it will return the correct number of records for the table and not the number of record of the last load.

           

          If that is not the case, can you provide an example?

          • Re: Check result of preceding LOAD statement?
            Celambarasan Adhimulam

            Im not sure abt the problem. Could you post the script here?

              • Re: Check result of preceding LOAD statement?
                Friedrich Hofmann

                Hi,

                 

                my script is actually quite simple. I provide it here. Beforehand, I have a LOAD from a qvd with a few WHERE clauses - that returns a handful of records. Then comes this:

                 

                // First I have to load what there is already in the historical qvd (difficult, on the first day there is nothing of course, so this

                // has to be deactivated.

                Historie_Sperr1_74:
                LOAD Datum_Sp_AOS,
                    
                Anz_Sachnrn
                FROM
                [..\01_QVD\Historische_qvd\Historie_Sperr1_74.qvd]
                (
                qvd);

                // Now the new contents (present day) from the qvd-LOAD have to be concatenated to that file.

                // I want to load only those records where DATE_DIFF>5 (this is calculated with the INTERVAL() fct.) - the items that are older than five days.

                // Here lies the problem since there are no items as yet which are older than 5 days, so there is nothing to be extracted.

                // In that case, I would like to just load one record with the present date (num. format) and the nr. 0


                CONCATENATE

                LOAD
                    
                $(v_today) as Datum_Sp_AOS,
                    
                count(DISTINCT BIN_ITEM_DETAIL.ITEM_NUMBER) as Anz_Sachnrn
                //     IF(IsNull(count(DISTINCT BIN_ITEM_DETAIL.ITEM_NUMBER)) = -1, 0, count(DISTINCT BIN_ITEM_DETAIL.ITEM_NUMBER))  as Anzahl_Sachnrn
                //     IF(count(DISTINCT BIN_ITEM_DETAIL.ITEM_NUMBER) <> 0, count(DISTINCT BIN_ITEM_DETAIL.ITEM_NUMBER), 0) as Anzahl_Sachnrn
                //     BIN_ITEM_DETAIL.ACTIVATION_DATE_DIFF
                RESIDENT Sperr1_AOS
                WHERE BIN_ITEM_DETAIL.ACTIVATION_DATE_DIFF > 5
                // GROUP BY $(v_today)
                ;

                 

                @ Celambarasan

                The problem, as described in the comment lines, is that as yet there is nothing to extract acc. to those criteria and QlikView is quite stubborn when it comes to values that are not there...

                 

                @ Gonzalo

                I had tried the fct yesterday and it didn't work - but I might have been doing something wrong. I'll try again.

                 

                Thanks a lot!

                Best regards,

                 

                DataNibbler

                  • Re: Re: Check result of preceding LOAD statement?
                    Jonathan Dienst

                    Hi

                     

                    You could use NoOfRows('Historie_Sperr1_74') on the table after the qvd load and store the result in a variable, then compare the NoOfRows() after the second load to see if the rowcount changed. Something like this:

                     

                    ... first load ...
                    Let vSize = NoOfRows('Historie_Sperr1_74');
                    ... second load
                    If NoOfRows('Historie_Sperr1_74') = vSize Then
                    ... concatenate row indicating no data 
                    End If
                    

                     

                    Alternatively, you could change the order of the load (do the qvd after the database load), or load the database data into a temp table and check the results before concatenating. (Don't do this last if the data is large!)

                     

                    Hope that helps

                    Jonathan

                      • Re: Check result of preceding LOAD statement?
                        Friedrich Hofmann

                        Hi Jonathan,

                         

                        the first part of your post looks very good.

                        Actually, I don't like the idea of loading that history_qvd twice - it's bad enough that we have to abuse QlikView to do a historization that our database_developers neglected - but I guess that will be the easiest way.

                        I can just load the qvd, then check the NoOfRows, then concatenate new contents from the qvd-LOAD and check again. Not sure that will work, but I can quickly check visually.

                         

                        I'm not sure I understand the second part of your post, but with checking the results of the qvd-LOAD (which would imply I'd have to apply all the WHERE clauses to it) I would be back to my core problem: That it would probably not load anything because there aren't any items that old yet and the NoOfRows() fct. seems not to work when nothing at all was loaded.

                         

                        I'll try that out now. I'd like to get that historization running asap so that when I can actually begin building the app, there will already be some data.

                         

                        Thanks a lot!

                        Best regards,

                         

                        DataNibbler

                         

                        P.S.: The core problem remains the same (with both of these solutions for I do not have any data yet): When I apply all the WHERE clauses, nothing at all is loaded, so the table does not exist and I cannot query it using the NoOfRows() fct.

                        Maybe I could enhance that query by first checking whether the table exists?

                          • Re: Check result of preceding LOAD statement?
                            Friedrich Hofmann


                            Hi Jonathan,

                             

                            it is getting stranger every time: Even though the script_execution_window tells me that 0 records were loaded and I cannot add any of the fields to listboxes - and I cannot query the NoOfRows - the NoOfTables() fct. tells me that one table was loaded... so that does not help me in finding out whether or not the LOAD was successful.

                            It seems though, that the NoOfRows() fct. will work on scripting_level though it does not on GUI_level.

                            I'll try using that.

                             

                            Best regards,

                             

                            DataNibbler