Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jumiprado
Creator
Creator

How to drop some fields

I have a table like this for each month

NameD.1D.2D.3D.4D.5D.6D.7D.8D.9D.10D.11D.12D.13D.14D.15D.16D.17D.18D.19D.20D.21D.22D.23D.24D.25D.26D.27D.28D.29D.30D.31
AAA00000000000000000000000002.3100000

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??

9 Replies
olivierrobin
Specialist III
Specialist III

hello

which error do you have ?

jumiprado
Creator
Creator
Author

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.

image009.png

olivierrobin
Specialist III
Specialist III

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;

jumiprado
Creator
Creator
Author

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

olivierrobin
Specialist III
Specialist III

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

jumiprado
Creator
Creator
Author

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!

olivierrobin
Specialist III
Specialist III

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)

......

olivierrobin
Specialist III
Specialist III

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;

olivierrobin
Specialist III
Specialist III

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