Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

for loop in qlikview with dates

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?

1 Solution

Accepted Solutions
MVP
MVP

Re: for loop in qlikview with dates

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/

20 Replies
Not applicable

Re: for loop in qlikview with dates

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
Honored Contributor II

Re: for loop in qlikview with dates

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

Not applicable

Re: for loop in qlikview with dates

Hi

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

Regards,

Janzen

Not applicable

Re: for loop in qlikview with dates

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 .

Re: for loop in qlikview with dates

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

Re: for loop in qlikview with dates

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

Hope it helps

Not applicable

Re: for loop in qlikview with dates

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

MVP
MVP

Re: for loop in qlikview with dates

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

Highlighted
MVP
MVP

Re: for loop in qlikview with dates

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;   

NEXT

Hope this helps you.

Regards,

Jagan.