Solved: Re: for loop in qlikview with dates - Qlik Community

Report Inappropriate Content · ‎2013-04-07

i have to do an extraction with date

i have right now something like this...

but it didnt work qlikview didnt show me errors and i dont know what happends

LET y=Today();

FOR i=Date('01/01/2012','DD/MM/YYYY') to $(y)

Cartera_COP:

//Query Statement

STORE * FROM Cartera_COP into ../01_Datos/Cartera_COP_$(i).qvd;

DROP Table Cartera_COP;

NEXT i

how i can do this?

stevedark · ‎2013-04-08

Hi Carlos,

The function you have for converting the string '01/01/2012' into a date is incorrect - the function Date is used for formatting a date field, whilst the function you require, Date#, converts a string to a number.

You could change the line to read:

FOR i=Date#('01/01/2012','DD/MM/YYYY') to $(y)

The suffix in the QVD file name if this worked, would be the integer number for the day (ie. today is 41372), and you probably want it in a YYYYMMDD format.

Try this syntax instead:

let vDays = num#(today() - date#('01/01/2012','DD/MM/YYYY'));

FOR i = 0 to vDays

let vSuffix = Date(today() - vDays + i, 'YYYYMMDD');

TRACE $(i) - $(vSuffix);

NEXT i

You can use $(vSuffix) in the same way as you do $(i) in your code above.

Hope that helps,

Steve

http://www.quickintelligence.co.uk/

View solution in original post

Report Inappropriate Content · ‎2013-04-08

Hi Carlos,

The Date('01/01/2012','DD/MM/YYYY') will return you a text value not date.

Try using MakeDate(2012,1,1) instead.

Regards,

Nadsky

sushil353 · ‎2013-04-08

Hi,

try this:

LET y=Today();

FOR i=Date('01/01/2012','DD/MM/YYYY') to '$(y)'

Cartera_COP:

//Query Statement

STORE * FROM Cartera_COP into ../01_Datos/Cartera_COP_$(i).qvd;

DROP Table Cartera_COP;

NEXT i

HTH

Sushil

Report Inappropriate Content · ‎2013-04-08

Hi

You need to have same format for both dates. You can use Date(Today(),'DD/MM/YYYY').

Regards,

Janzen

Report Inappropriate Content · ‎2013-04-08

Hi ,

Try the below code ...

Let TodayDate = fabs(Today());

Let FromDate=fabs( Date('01/01/2012','DD/MM/YYYY'));

Cartera_COP:

LOAD

Date($(FromDate)+recno()-1, 'DD/MM/YYYY') as "Date"

AUTOGENERATE $(TodayDate)-$(FromDate)+1;

Please let me know if there is anything .

kaushiknsolanki · ‎2013-04-08

Hi,

I am not clear about your requirement.

By looking at script i understand you want to Store the data for each date, but in your script you have no where used the date as filter.

Kindly explain with example.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

alexandros17 · ‎2013-04-08

The NUM function converts dates to correspondant numbers so you can loop throught numbers.

Hope it helps

Report Inappropriate Content · ‎2013-04-08

I think is because filename . You can't put "/' char in a filename (at filename you have a variable date with "/" format in ).

swuehl · ‎2013-04-08

In general, if you need to debug your code, try entering the debug mode from script editor and step through your code, having a look to your variables in particular.

I assume you are running into troubles because your For .. Next expressions for start / end won't evaluate to integers (which I believe you'll need here). Try using numerical integer representation for your loop dates:

LET y = num(Today());

LET x = num(makedate(2012,1,1));

FOR i = x to y

Cartera_COP:

//Query Statement

LET vDate = Date($(i),'DD/MM/YYYY');

STORE * FROM Cartera_COP into ../01_Datos/Cartera_COP_$(vDate).qvd (qvd);

DROP Table Cartera_COP;

NEXT i

jagan · ‎2013-04-08

Hi,

Try like this, it is working for me

For i = vStartNum to vTotalRows

Table1:

Load

Column1,

Column2

FROM TableName;

STORE Table1 INTO QVDFileName.qvd;

Drop Table Table1;

Regards,

Jagan.