topic Re: Scripting Issue in QlikView

Scripting Issue

Wed, 19 Nov 2014 12:05:28 GMT

I have generated yearly QVDs based on the year field present in the file.

Now, i have to store the QVDs prior to 2013 into an archive folder and the QVDs after 2013 in the current folder.

I have used the below code for generating yearly QVDs

temp:

load min(Year) as MinY,

max(Year) as MaxY

resident tab;

let MI= peek('MinY', 0 , 'temp');

let MA= peek('MaxY' , 0, 'temp');

for i= $(MI) to $(MA)

tabtemp:

noconcatenate

load * resident tab where Year=$(i);

store * from tabtemp into ../../Abc_$(i).qvd(qvd)

drop table tabtemp;

How do i modify the above code so that it stores the QVDs prior to 2013 in Archvie folder and the QVDs genertaed after 2013 in another folder.

Re: Scripting Issue

marcus_malinow — Wed, 19 Nov 2014 12:07:35 GMT

if $(I) <2013 then

store tabtemp into ..\archive\Abc_$(i).qvd (qvd);

else

store tabltemp into ..\othefolder\Abc_$(I).qvd (qvd);

endif

Re: Scripting Issue

Wed, 19 Nov 2014 12:21:10 GMT

Hi Marcus,

When i am using if else in the below code,

for i =$(MI) to $(MA)

tabtemp:

noconcatenate

load * resident tab where Year=$(i);

if $(i) >=year(today()) -1 then

store * from tabtemp into ../.. ABC_$(i).qvd(qvd);

else

store * from tabtemp into ../../ABC_$(i).qvd(qvd);

endif

In the above case the qvds are getting stored into different folders, but all the QVDs contain the data only for 2012 i.e. the minimum year.

When if else statement is removed it works fine.

Re: Scripting Issue

marcus_malinow — Wed, 19 Nov 2014 12:26:11 GMT

ok, in the code above you have a syntax issue on your first store statement

store * from tabtemp into ../.. ABC_$(I).qvd (qvd);

Re: Scripting Issue

marcus_malinow — Wed, 19 Nov 2014 12:28:16 GMT

I'd also maybe add a trace statement inside the loop.

Trace $(i);

Just to make sure your MI and MA variables are being set correctly.

Re: Scripting Issue

Wed, 19 Nov 2014 12:34:56 GMT

But when the if else part is removed from the code, it works perfectly fine...

Re: Scripting Issue

perumal_41 — Wed, 19 Nov 2014 12:37:04 GMT

GHi

Try below

for i =$(MI) to $(MA)

tabtemp$(i):

noconcatenate

load * resident tab where Year=$(i);

if $(i) >=year(today()) -1 then

store * from tabtemp$(i) into ../.. ABC_$(i).qvd(qvd);

else

store * from tabtemp$(i) into ../../ABC_$(i).qvd(qvd);

endif

Re: Scripting Issue

marcus_malinow — Wed, 19 Nov 2014 12:37:32 GMT

ok possibly an issue on your if condition

Try

if $(i) >= (Year(Today()) - 1) then

Re: Scripting Issue

marcus_malinow — Wed, 19 Nov 2014 12:40:00 GMT

also add

drop table tabtemp;

after the endif

Re: Scripting Issue

marcus_malinow — Wed, 19 Nov 2014 15:27:21 GMT

Is this now working?

If so, please mark as answered.

Re: Scripting Issue

Thu, 20 Nov 2014 10:23:14 GMT

Thanks Marcus!!! I made a slight change in the code too...

Instead of using if i>=(year(today())-3) , i stored year(today()-3) in a variable var and in the if statement , i used if $(i)>=$(var)