Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jumiprado
Contributor

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

Tags (1)
9 Replies
Highlighted
olivierrobin
Valued Contributor III

Re: How to drop some fields

hello

which error do you have ?

Highlighted
jumiprado
Contributor

Re: How to drop some fields

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

Highlighted
olivierrobin
Valued Contributor III

Re: How to drop some fields

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;

Highlighted
jumiprado
Contributor

Re: How to drop some fields

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

Highlighted
olivierrobin
Valued Contributor III

Re: How to drop some fields

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

Highlighted
jumiprado
Contributor

Re: How to drop some fields

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!

Highlighted
olivierrobin
Valued Contributor III

Re: How to drop some fields

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)

......

Highlighted
olivierrobin
Valued Contributor III

Re: How to drop some fields

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;

Highlighted
olivierrobin
Valued Contributor III

Re: How to drop some fields

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