topic Re: Help with Loop in QlikView

Help with Loop

hopkinsc — Fri, 23 Sep 2016 09:03:55 GMT

Hi all,

i am currently implementing an incremental load into an existing extract script.

here is a brief description on how my client wants it to work.

an admin user would update a field in the db with the amount of previous years to go back to extract.

Transaction_Years = 2

so far i have added a variable for the max year (taken from the list of distinct years in the db and a variable for the Transaction_Year that has been entered by the admin person.

vMaxYear = 2016

vTransaction_Year = 2

so what i want is to run a loop which loads all data for those years (2014,2015,2016) and stores each year into its own QVD.

apologies but i can't get my head around how to do this.

can anyone help please?

Re: Help with Loop

Digvijay_Singh — Fri, 23 Sep 2016 09:14:32 GMT

Something like -

For each vYear in $(vMaxYear),$(vMaxYear-1),$(vMaxYear-2)

$(vYear)QVD:

Select ....

From DB

Where (Year,vYear)

Store $(vYear)QVD into Path/$(vYear)QVD

Just an idea, it would need improvement to make it work

Re: Help with Loop

jonathandienst — Fri, 23 Sep 2016 09:20:44 GMT

Something like:

//Create the QVDs

For i = (vMaxYear - vTransaction_Year + 1) To vMaxYear

Data:

SQL SELECT *

FROM SourceTable

WHERE Year = $(i);

STORE Data into (qvd);

DROP Table Data;

Next

Adjust the SQL SELECT and the QVD file name and path to match your requirements

Re: Help with Loop

hopkinsc — Fri, 23 Sep 2016 09:46:19 GMT

Thanks Jonathan, thats exactly what i needed.