Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
mwoolf
Honored Contributor II

Re: for loop in qlikview with dates

the / is not a legal character in filenames.

Re: for loop in qlikview with dates

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

dagomezl
Contributor III

Re: for loop in qlikview with dates

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

Not applicable

Re: for loop in qlikview with dates

//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;

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/

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 .

Highlighted
Not applicable

Re: for loop in qlikview with dates

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

MVP
MVP

Re: for loop in qlikview with dates

Glad to have been able to help.

- Steve

Not applicable

Re: for loop in qlikview with dates

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

MVP
MVP

Re: for loop in qlikview with dates

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

Community Browser