Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table like this for each month
Name | D.1 | D.2 | D.3 | D.4 | D.5 | D.6 | D.7 | D.8 | D.9 | D.10 | D.11 | D.12 | D.13 | D.14 | D.15 | D.16 | D.17 | D.18 | D.19 | D.20 | D.21 | D.22 | D.23 | D.24 | D.25 | D.26 | D.27 | D.28 | D.29 | D.30 | D.31 |
AAA | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2.31 | 0 | 0 | 0 | 0 | 0 |
I made this script
FOR EACH nom_file IN FILELIST('$(MODELED_Folder)\*.xls')
Table_Tmp:
*
FROM [$(nom_file)] (ooxml, embedded labels,no eof, no quotes);
NEXT
Table_1:
CrossTable(Dia_, Hs_Trabajadas, 19) LOAD
*
Resident Table_Tmp;
drop table Table_Tmp;
The problem is that i have month with 31 days, other with 30 and feb with 28 days, i make a date with the name of the excel file and i take de num from field "Dia_" created with the crosstable.
When i Have tha final field "DATE" i have an error with that dates with days<31
Can you help me??
hello
which error do you have ?
I have this problem,
The field "Fecha" is from excel namefile, then I have columns, in each file, named D.1, D.2, D.3....D.31 instead of month, ex: i have D.31 for February but that month only have 28 days, so i need to drop that days made from the script or take a best way to make a field "FechaInas" with the info i have.
you can use num(date#()) to test if a date is valid or not
before transforming your field in date
e.g (with date format dd/mm/yyyy:
let a='11/07/2018';
let b=num(date#(a));
trace *** $(a) *** $(b) ***;
let a='41/07/2018';
let b=num(date#(a));
trace *** $(a) *** $(b) ***;
exit script;
Olivier, thanks for the answer but i cant understand how to do that.
I have this script, can you help me with this?
FOR EACH nom_file IN FILELIST('$(MODELED_Folder)\*.xls')
SIGLyC_Tmp:
LOAD
ID,
Date(MakeDate(
left( FileBaseName(),4),
Right ( FileBaseName(),2)),'MM/YYYY') as Fecha,
D.1,
D.2,
D.3,
D.4,
D.5,
D.6,
D.7,
D.8,
D.9,
D.10,
D.11,
D.12,
D.13,
D.14,
D.15,
D.16,
D.17,
D.18,
D.19,
D.20,
D.21,
D.22,
D.23,
D.24,
D.25,
D.26,
D.27,
D.28,
D.29,
D.30,
D.31
FROM [$(nom_file)] (ooxml, embedded labels,no eof, no quotes);
NEXT
//Genero una tabla que pivotee las columnas de días para obtener un registro por día para cada empleado.
SIGLyC_1:
CrossTable(Dia_, Hs_Trabajadas, 19)
LOAD
*
Resident SIGLyC_Tmp;
drop table SIGLyC_Tmp;
//Genero una tabla que genere la FechaInas en base al nombre del archivo y al número del capmo Dia_ ej: D.1 es 1
SIGLyC_2:
load *,
DATE(DATE#(num(keepchar(Dia_,'0123456789'),'00')&'/'&Fecha,'DD/MM/YYYY')) as FechaInas
Resident SIGLyC_1;
drop table SIGLyC_1;
exit
script
hello
that seems good.
the only thing is
DATE(DATE#(num(keepchar(Dia_,'0123456789'),'00')&'/'&Fecha,'DD/MM/YYYY')) as FechaInas
why don't you take the 1st only.
Whatever month you process, there is always day 1
Olivier, the problem is that the excels files bring me fields from D.1 to D.31 independently if the month have 30, 31 or 28 days, so when i generate a Date per day, for february for example i have 3 days more so create days for the next month.
I hope i have been clear in my explanation.
Regards!
something you could do before your load is creating dynamically the list of days to load
something like (pseudoc ode not QV code)
let listdays='';
for i=1 to numberofdays(month of my file)
let listdays=listdays & 'D.' & i & ','
next
let listdays=left(listdays,len(listdays)-1);
LOAD
ID,
Date(MakeDate(
left( FileBaseName(),4),
Right ( FileBaseName(),2)),'MM/YYYY') as Fecha,
$(listdays)
......
hello
at least, I done something like that
t:
load
'02/2018' as mois,*
;
load * inline [
Name D.1 D.2 D.3 D.4 D.5 D.6 D.7 D.8 D.9 D.10 D.11 D.12 D.13 D.14 D.15 D.16 D.17 D.18 D.19 D.20 D.21 D.22 D.23 D.24 D.25 D.26 D.27 D.28 D.29 D.30 D.31
AAA 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2.31 0 0 0 0 0
] (delimiter is '\t');
t2:
crosstable(Jour,Valeur,2) load * resident t;
drop table t;
t3:
NoConcatenate
load *
resident t2
where month(date(subfield(Jour,'.',2)&'/'&mois,'DD/MM/YYYY') )=subfield(mois,'/',1);
drop table t2;
or another way (the only one which seems to to test correctly a date) is to use makedate()
where not(isnull(makedate(subfield(mois,'/',2),subfield(mois,'/',1),subfield(Jour,'.',2))));