topic Re: Load from only certain txt files based on file name in QlikView

Load from only certain txt files based on file name

gerhardl — Thu, 15 Aug 2013 11:45:12 GMT

Hello,

I am loading data from a folder with dozens of text files. The file names contain the dates in the format DD-MMM-YYYY, like this:

Weekly_TransactionExtract_A_05-AUG-2013_11-AUG-2013_12082013045253.txt

Also see the image attached.

I am only loading certain records in the file (where exists [Account no]) - but the load takes very long because it is running through all these files.

I want to add a variable to the start of the load script which is a start date of the particular campaign the QV File is reporting on. Then I want the load script to only load FROM files where the date in the file name is bigger than this variable.

Does that make sense? Please help.

Part of Load Script also attached.

Thanks,

Gerhard

Re: Load from only certain txt files based on file name

Gysbert_Wassenaar — Thu, 15 Aug 2013 12:42:45 GMT

Maybe something like this:

LET vRefDate = num(today());
Set vConcatenate = ;
sub ScanFolder(Root)
     for each FileExtension in 'txt'
          for each FoundFile in filelist( Root & '\*.' & FileExtension)
               FileList:
               $(vConcatenate)
                    if( date#(mid('$(FoundFile)',29,12),'DD-MMM-YYYY') > $(vRefDate)) then
     
                         LOAD *, '$(FoundFile)' as SourceFile
                         FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);                              
                         Set vConcatenate = Concatenate;
                     end if
               next FoundFile
     next FileExtension
end sub
Call ScanFolder('C:\Work\QV Apps') ;

Re: Load from only certain txt files based on file name

gerhardl — Thu, 15 Aug 2013 13:31:23 GMT

Sorry this is a bit above my level....should that entire block of code go before all of my Loads?

in the attached images you can see my 4 tabs. I only want to use this for the "Transaction" and the "Applications" tabs, in which I load from different folders - images 3 and 4.

So should I add your code to my main tab where my variables etc are set? I'm lost...