topic Re: for loop in qlikview with dates in QlikView

for loop in qlikview with dates

Sun, 07 Apr 2013 23:51:08 GMT

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?

Re: for loop in qlikview with dates

Mon, 08 Apr 2013 04:51:02 GMT

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

Re: for loop in qlikview with dates

sushil353 — Mon, 08 Apr 2013 05:22:32 GMT

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

Re: for loop in qlikview with dates

Mon, 08 Apr 2013 05:52:01 GMT

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

Regards,

Janzen

Re: for loop in qlikview with dates

Mon, 08 Apr 2013 06:09:46 GMT

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

kaushiknsolanki — Mon, 08 Apr 2013 06:17:11 GMT

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

alexandros17 — Mon, 08 Apr 2013 07:10:18 GMT

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

Hope it helps

Re: for loop in qlikview with dates

Mon, 08 Apr 2013 08:56:59 GMT

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

Re: for loop in qlikview with dates

swuehl — Mon, 08 Apr 2013 10:15:09 GMT

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

Re: for loop in qlikview with dates

jagan — Mon, 08 Apr 2013 10:48:58 GMT

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;

Hope this helps you.

Regards,

Jagan.

Re: for loop in qlikview with dates

m_woolf — Mon, 08 Apr 2013 11:07:52 GMT

the / is not a legal character in filenames.

Re: for loop in qlikview with dates

marcus_sommer — Mon, 08 Apr 2013 14:01:34 GMT

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

Re: for loop in qlikview with dates

Anonymous — Mon, 08 Apr 2013 16:34:40 GMT

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

Re: for loop in qlikview with dates

Mon, 08 Apr 2013 16:41:57 GMT

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

Re: for loop in qlikview with dates

stevedark — Mon, 08 Apr 2013 17:35:23 GMT

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/

Re: for loop in qlikview with dates

Mon, 08 Apr 2013 17:55:12 GMT

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

Mon, 08 Apr 2013 18:33:06 GMT

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

Re: for loop in qlikview with dates

stevedark — Mon, 08 Apr 2013 19:17:19 GMT

Glad to have been able to help.

- Steve

Re: for loop in qlikview with dates

Tue, 30 Sep 2014 20:24:52 GMT

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

Re: for loop in qlikview with dates

stevedark — Tue, 30 Sep 2014 21:39:01 GMT

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