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.
// 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
$(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
WHERE BIN_ITEM_DETAIL.ACTIVATION_DATE_DIFF > 5
// GROUP BY $(v_today)
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...
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!
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
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!
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.
I'll try using that.
it seems to be working finally.
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.
Glad you have managed to solve your problem.
For the record, you can use NoOfRows when the table may not exist, by wrapping NoOfRows in an Alt() function, like this:
Let zSize = Alt(NoOfRows('MyTable'), 0);
This will return 0 if the table does not exist. Change the 0 to -1 to return a negative number if the table does not exist.