Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
datanibbler
Esteemed Contributor

Check result of preceding LOAD statement?


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.

9 Replies
Not applicable

Re: Check result of preceding LOAD statement?

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?

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

datanibbler
Esteemed Contributor

Re: Check result of preceding LOAD statement?

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

MVP
MVP

Re: Re: Check result of preceding LOAD statement?

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
datanibbler
Esteemed Contributor

Re: Check result of preceding LOAD statement?

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?

datanibbler
Esteemed Contributor

Re: Check result of preceding LOAD statement?


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

datanibbler
Esteemed Contributor

Re: Check result of preceding LOAD statement?


Hi,

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.

Best regards,

DataNibbler

MVP
MVP

Re: Check result of preceding LOAD statement?

Hi

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.

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
datanibbler
Esteemed Contributor

Re: Check result of preceding LOAD statement?

Hi Jonathan,

that is indeed interesting - I didn't know this fct. before.

Thanks!

Best regards,

DataNibbler

Community Browser