topic Re: How to drop some fields in QlikView

How to drop some fields

jumiprado — Tue, 10 Jul 2018 04:51:12 GMT

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

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

Re: How to drop some fields

olivierrobin — Tue, 10 Jul 2018 07:45:26 GMT

hello

which error do you have ?

Re: How to drop some fields

jumiprado — Tue, 10 Jul 2018 15:19:54 GMT

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.

Re: How to drop some fields

olivierrobin — Tue, 10 Jul 2018 15:29:56 GMT

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;

Re: How to drop some fields

jumiprado — Thu, 12 Jul 2018 14:01:03 GMT

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

Re: How to drop some fields

olivierrobin — Thu, 12 Jul 2018 14:52:21 GMT

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

Re: How to drop some fields

jumiprado — Thu, 12 Jul 2018 16:01:04 GMT

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!

Re: How to drop some fields

olivierrobin — Thu, 12 Jul 2018 16:13:13 GMT

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 & ','

let listdays=left(listdays,len(listdays)-1);

LOAD
ID,
Date(MakeDate(
left( FileBaseName(),4),
Right ( FileBaseName(),2)),'MM/YYYY') as Fecha,

$(listdays)

......

Re: How to drop some fields

olivierrobin — Fri, 13 Jul 2018 07:31:46 GMT

hello

at least, I done something like that

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;

Re: How to drop some fields

olivierrobin — Fri, 13 Jul 2018 07:46:27 GMT

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