- 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!
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.
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
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
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
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!
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?
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.
The NoOfRows() fct. on scripting level works - I checked both ways - and I can use it to check whether Qlikview loaded any records from the database and, if not, create a record and do a RESIDENT LOAD with the FIRST1 parameter from a calendar I created beforehand.
Now I have one real record to start with, so from now on I can, every day, just load this and check whether there is anything new from the database to add - if not, I do the same again and add a constructed record - and store it again.