topic Re: Conditional reload in QlikView

Conditional reload

Thu, 29 Apr 2010 10:47:10 GMT

Hi,

This is the case. I need to do conditional reload. I have a table with last source database update date. This is one row table. i need the script to compare two dates - todays date and last database update date. If todays date =last update date i want the script to reload data, if not I want to exit script and save the appliaction with old data.

Any ideas?

Conditional reload

sparur — Thu, 29 Apr 2010 10:58:30 GMT

Hello.

If I understand, you need such as:

tmpLastUpdateDate:

LOAD [Last Update Date]; SQL SELECT "Last Update Date" FROM SystemTable;

LET vLastUpdDate = peek('Last Update Date', 0);

IF $(vLastUpdDate = Today(1) THEN

//load your data from DB

LOAD ... FROM...

ELSE

exit script;

END IF;

Conditional reload

Thu, 29 Apr 2010 11:08:44 GMT

Thanks. It seems to be good idea for one LOAD statement but I am not sure if it will work with whole script (more LOAD statements). Can I put more load statements sth like this

IF $(vLastUpdDate = Today(1) THEN

//load your data from DB

LOAD ... FROM...

ELSE

exit script;

END IF;

???

Conditional reload

sparur — Thu, 29 Apr 2010 11:19:34 GMT

Of course, you can.

Conditional reload

prabhu0505 — Thu, 29 Apr 2010 12:17:57 GMT

This is an example from reference manua page no. 502



QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#

AND ModificationTime < #$(BeginningThisExecTime)#;

Concatenate

LOAD PrimaryKey, X, Y FROM File.QVD;

STORE QV_Table INTO File.QVD;

I hope this may be useful.

Conditional reload

Mon, 16 Aug 2010 09:29:02 GMT

Hey michal,

How did you achieve this?

For me the exit script will exit from the script and save the qvw with no data.

How can I retain the old data? ie, how can exit from script without saving the document?

Regards,

Robins.

PS: I am reloading the document using QV 9.0 Server Management console.

Re: Conditional reload

Anonymous — Tue, 04 Jun 2013 00:12:28 GMT

Hi,

I am trying to do a similar thing. Test my table and if happy let the standard reloads occur.

But, if my test of the table fails I want to exit the script without saving so that the last good version stays good.

The code below checks that the table has >= 44 million rows. The IF statement works and exits the script but wipes all content from the current tables.

I need to keep the content of my QVW if the script exits.

How do I do this?

LOAD

ROWCounted;

SQL SELECT COUNT(*) AS ROWCounted

FROM [HealthCentral].[dbo].[WaitingListHistory];

LET vRowCount = peek('ROWCounted',0);

IF $(vRowCount) < 44000000000 THEN

Exit Script;

END IF

Re: Conditional reload

Tue, 04 Jun 2013 02:12:40 GMT

Hi John,

In your instance, Prabhu's example would like something like this:

LOAD

ROWCounted;

SQL SELECT COUNT(*) AS ROWCounted

FROM [HealthCentral].[dbo].[WaitingListHistory];

LET vRowCount = peek('ROWCounted',0);

IF $(vRowCount) < 44000000000 THEN

WaitingListHistory:

LOAD a, b, c FROM File.QVD (QVD);

ELSE

WaitingListHistory:

LOAD

SQL SELECT a, b, c FROM [dbo].[WaitingListHistory];

END IF

STORE WaitingListHistory INTO File.qvd (QVD);

Re: Conditional reload

Anonymous — Tue, 04 Jun 2013 02:25:45 GMT

Michael,

thank you for the reply.

In this instance I am looking for indicators that the source table is incomplete. Where I believe this to be the case I want to cancel the reload process entirely and keep the last loaded data.

There was reference to using Error Codes to trigger an exit without save and I was hoping someone had an example like that.

Cheers,

John.

Re: Conditional reload

Tue, 04 Jun 2013 02:34:00 GMT

I see. That does sound like a neater solution if you have multiple tables to check and load.

Re: Conditional reload

Anonymous — Tue, 04 Jun 2013 03:02:42 GMT

Michael,

I worked it out.

instead of an exit script I just place a LOAD statement which references an unknown table. This causes an error which stops the reload without saving changes.

Cheers,

John.

-------------

LOAD

ROWCounted;

SQL SELECT COUNT(*) AS ROWCounted

FROM [HealthCentral].[dbo].[WaitingListHistory];

LET vRowCount = peek('ROWCounted',0);

IF $(vRowCount) < 44000000 THEN

LOAD x RESIDENT y;

END IF