topic Creating an iteration based on last update date in QlikView

Creating an iteration based on last update date

Mon, 30 Apr 2012 10:02:17 GMT

hi,

im trying to create a script that will extract only selected rows that match a date. somthing like:

LOAD <column names>;

SQL SELECT *

FROM <table name> where LAST_MODIFIED_DATE>=LastLoadTime ;

the script should start with:

LoadTime:

LOAD now() as NewLoadTime

AutoGenerate 1;

STORE LoadTime into <my .QVD file>;

drop table LoadTime;

next, run the above script where "last load time" will be the last time the script was ran,

and than, if the script is finished succesfuly, insert the "new load time" into the "last load time" and store it for next run.

any ideas?

thnx

Re: Creating an iteration based on last update date

Mon, 30 Apr 2012 10:13:03 GMT

Hi,

what i understand from your post is "everytime the LastLoadTime should update with the latest LoadedTime.

Please use the below script. I hope it might helps you.

LOAD <column names>;

SQL SELECT *

FROM <table name> where LAST_MODIFIED_DATE>=LastLoadTime ;

LoadTime:

LOAD now() as NewLoadTime

AutoGenerate 1;

If ScriptErrorCount = 0 then

STORE LoadTime into <my .QVD file>;

Let LastLoadTime = ReloadTime() ;

End If

drop table LoadTime;

Regards

Venkat

Creating an iteration based on last update date

Mon, 30 Apr 2012 10:30:38 GMT

thanks Venkart,

that helped but what i need first is how to relate to the 'LastLoadTime' within the SQL query?

should i use resident to first upload the "LoadTime" table?

how can the SQL query relate to the value within the 'LastLoadTime' field?

thnx

Re: Creating an iteration based on last update date

Mon, 30 Apr 2012 11:07:27 GMT

Hi,

Please can you provide brief explanation of what you are trying to achieve.

Whether you are doing any "Incremental Procedure" ?

Regards

venkat

Creating an iteration based on last update date

Mon, 30 Apr 2012 11:21:20 GMT

hi again,

well, its simple:

i have a table that does not hold history and if a transaction has a new version than it overruns the last one.

what i want to do is to run a first data extraction of all data.

then, from that point and on, extract only transactions with "lastUpdateDate" greater than last reload time.

that way, i want to hold within QV the history of all versions since first extraction.

around all the above, i would like to check if the run was succesfull. if yes, keep the new reload time, else keep the old reload time and next time extract from that point and on.

now, from digging in the forums, i got to solution:

1. create a new QVD file for each new run

2. concatenate new extracted data to old QVD file so that one file will contain it all...

im not sure yet which one is better for my need.

take in mind that next step will be to calculate the time interval between two versions so that i can analize these intervals...

Re: Creating an iteration based on last update date

Mon, 30 Apr 2012 12:06:10 GMT

Hi,

Please find the attached text document that will provide you brief knowledge of creating an Initial and Incremental load data.

I hope this will help you.

If the code satisfies your requirements please do the changes in the code as per your required.

Regards

Venkat