topic Re: Scripting Issue in QlikView

Scripting Issue

Tue, 11 Nov 2014 12:48:49 GMT

There is a folder in which there are a number of csv files present. In each of the csv files, there is a field Year present.

I have to load all those files from the folder where the values of Year field > current year value -3 years.

Please help...

Re: Scripting Issue

alexandros17 — Tue, 11 Nov 2014 12:57:45 GMT

the easiest thing is:

load all the files then load them again where FieldYear >= year(Today())-3

Re: Scripting Issue

ali_hijazi — Tue, 11 Nov 2014 12:58:33 GMT

Set vConcatenate = ;

sub ScanFolder(Root)

for each FileExtension in 'csv'

for each FoundFile in filelist( Root & '\*.' & FileExtension)

FileList:

$(vConcatenate)

LOAD *, '$(FoundFile)' as SourceFile

FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

Set vConcatenate = Concatenate;

next FoundFile

next FileExtension

for each SubDirectory in dirlist( Root & '\*' )

call ScanFolder(SubDirectory)

next SubDirectory

end sub

Call ScanFolder('C:\Users\hic\Documents\2012\Work\QV Apps\DoDir') ;

then after reading the files in the folder

FinalTable:

noConcatenate

load * from FileList where Year > year(Today)-3;

drop table FileList

note: the lines in bold may differ in your case

just read one file manually and see what QlikViews writes for you

Re: Scripting Issue

Gysbert_Wassenaar — Tue, 11 Nov 2014 12:58:39 GMT

Well, you'll have to read the files to find out the value of the Year field. So, easiest is simply to read them all and then discard the records you don't need. Something like this should work:

LET vYear = year(today());

Years:

LOAD $(vYear) +1 - recno() as Year

AUTOGENERATE(4);

left keep

LOAD *

FROM *.CSV

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);;