for loop in qlikview with dates

[#1]

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?

[m_woolf]

the / is not a legal character in filenames.

[marcus_sommer]

Date('01/01/2012','DD/MM/YYYY') had probably not the same format as $(y) and hence the for-condition is not once true. I use for similar tasks numeric values like num(today()) and only NEXT instead NEXT i.

- Marcus

[Anonymous]

sorry for my English,

In the function FOR the variable be numeric.

Then, the code would also

LET y= Num(Today());

LET vFechaInicio = Num(MakeDate(2012,01,01));

FOR i=$(vFechaInicio) to $(y)

Cartera_COP:

//Query Statement

DROP Table Cartera_COP;

NEXT i

---------------- Original -------------------

Pido disculpas por mi ingles,

en la función FOR la variable debe ser numerica

entonces, el código debería ser así:

LET y= Num(Today());

LET vFechaInicio = Num(MakeDate(2012,01,01));

FOR i=$(vFechaInicio) to $(y)

Cartera_COP:

//Query Statement

DROP Table Cartera_COP;

NEXT i

[#5]

//I do it this way, to load the last ndays (for example 30 days)

//yo lo hago de esta manera, par acargar los ultimos ndias (para el ejemplo 30 dias)

let fecha_re =   Today();

set ndias = 30;

for a= $(ndias) to 1 step -1

          let          carga_fecha =  fecha_re- Interval(a);

          let          carga_f =  year(carga_fecha)& Right('00'&Num(Month(carga_fecha)),2) & Right('00'&day(carga_fecha),2);

          Dat_Areas:

          LOAD "empresa_codigo" as empresa_codigo,

              fecha as fecha,

              "Area_Codigo" as Area_Codigo,

              "Area_Jefe" as Area_Jefe,

              "Area_Orden" as Area_Orden,

              Area_Padre as Area_Padre;

          SQL SELECT 3 as empresa_codigo,

              fecha,

              Area_Codigo,

              Area_Jefe,

              Area_Orden,

              Area_Jefe as Area_Padre

          FROM Dat_Areas (nolock)

          where fecha=convert(datetime,'$(carga_f)',103);

          store Dat_Areas into $(rpath)\qvdch\parcialch\Dat_Areas$(carga_f).qvd;

          drop table Dat_Areas;

next;

[stevedark · Accepted solution]

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/

[#7]

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 .

[#8]

thanks,

dagomezl and Steve Dark i use both advices...

LET y=num(Today());

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

Cartera_COP:

//Query Statement

let z=date($(i),'DD_MM_YYYY'); // i had to put this line to didnt have problems with the file name.

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

DROP Table Cartera_COP;

NEXT i

[stevedark]

Glad to have been able to help.

- Steve

[#10]

This for loop worked out great!

let vDays = num#((today()-1) - date#('1/01/2014','MM/DD/YYYY'));
FOR i= 0 to vDays
let vSuffix = Date((today()-1) - vDays + i, 'MM/DD/YYYY');

LOAD data

  NEXT i

BUT I need to tweek it to jump by week, rather than loop each day..

Any suggestions on how I can accomplish that?

Thank you in advance!

Liz

[stevedark]

Hi Liz,

Simply divide the number of days you need to loop for by seven, and multiply the amount you step:

let vDays = num#((today()-1) - date#('1/01/2014','MM/DD/YYYY'));
FOR i= 0 to ceil(vDays / 7)
let vSuffix = Date((today()-1) - vDays + (i * 7), 'MM/DD/YYYY');

LOAD data

  NEXT i

You may need to tweak the bounds checking on the loop (eg. use Floor instead of Ceil) but this should get you close.

Hope that helps,

Steve