Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
the / is not a legal character in filenames.
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
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
//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;
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
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 .
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
Glad to have been able to help.
- Steve
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
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