Load file based on timestamp

[gerhardl]

Hi,

Could someone please help me to change the below load script so that it will only load files where the first date in the file name (format DD-MMM-YYYY) is bigger (newer) than a variable date that must be set at thestart of the load script?

Load [Appl No],

     [Product No],

     Date(Date#( left( [Decision Date],11), 'DD-MMM-YYYY')) as [Decision Date],

     if([Decicion Result]='A','Approved','Declined') as [Decision Result],

     ApplyMap('PQVolumeMap',[Champion Challenge No],'Unknown') as [PQ App Volume], 

     ApplyMap('PQVolumeMap2',[Champion Challenge No],'Unknown') as [PQ App Volume (Campaign Only)],

     [Champion Challenge No] as [Champion Challenge No App Extract],

     [Champion Challenge Desc]

FROM

(txt, codepage is 1252, embedded labels, delimiter is '~', no quotes)

WHERE [Champion Challenge No] >= $(vPQLow) AND [Champion Challenge No] <= $(vPQHigh);

A file will be named like this:

Weekly_ApplicationExtract_ACKS_05-AUG-2013_11-AUG-2013_12082013045043.txt

Could I please request that you help me with a full load script (end-to-end), as this is brand new territory for me.

Thanks so much,

Gerhard

[#2 · Accepted solution]

You could do it this way:

// Variable for counting the number of files loaded:

LET FileCount    = 0;

// Variable to set the minimun file date:

LET MinFileDate    = Date(Today());

FOR Each File in FileList ('Folder\*.txt');

    LET FileDate     = Date(Date#(Left(Right( '$(File)' ,42),11), 'DD-MMM-YYYY'));

    IF $(FileDate)>$(MinFileDate) THEN

        LET FileCount = $(FileCount)+1;

        TRACE Loading $(File) ($(FileDate));

        MyTable:

        LOAD * From $(File);

    ENDIF

NEXT File;

TRACE No more Files. $(FileCount) files loaded;

In this example, all the .txt are located in a folder called 'Folder', in the same root as the .qvw.Good luck.

[pgrenier]

Good day Gerhard,

My first instinct would be to first analyse the contents of the directory by loading all filenames into a table, and then loop through the contents of that table to select the appropriate filenames and execute a LOAD command on those.

I would like to invite you to take a look at the CALL instruction in QlikView's in program help (F1), where a nice example on constructing a DoDir procedure would help you accomplish your goal:

LET vRoot = 'C:\Users\tenba1\Documents\QlikView\Master File Extracts\Ackermans\Application Extracts\Application Extracts (QVD) - ACKERMANS\3';

SUB DoDir (Root)
   FOR Each File in FileList(Root&'\*.txt')
     Files:
     LOAD '$(File)' as File_Name
     AutoGenerate 1;
   NEXT File
ENDSUB

CALL DoDir (vRoot)

FOR i=0 to NoOfRows('Files')-1 
   LET vFileName = Peek('File_Name', 0, 'Files');
   IF Date(Date#(SubField(vFileName, '_', 4), 'DD-MMM-YYYY')) >= vDateFrom and Date(Date#(SubField(vFileName, '_', 5), 'DD-MMM-YYYY')) <= vDateTo then
      AppExtracts:
      LOAD *
      From [$(vRoot)\$(vFileName)];
   ENDIF
NEXT i;

P.S. Please bear in mind I didn't test this script, it might still need slight adjustments 😉

[gerhardl]

Hi there,

I currently have the below, but it is not working. It loads my first table (Accounts  - see image 1) and then "Execution of script failed. Reload old data?":

// Variable for counting the number of files loaded:

LET FileCount    = 0;

// Variable to set the minimun file date:

LET MinFileDate    = Date(Today()-10);

FOR Each File in FileList ('C:\Users\tenba1\Documents\QlikView\Master File Extracts\Transaction Extracts (QVD)\*.txt');

    LET FileDate     = Date(Date#(Left(Right( '$(File)' ,42),11), 'DD-MMM-YYYY'));

    IF $(FileDate)>$(MinFileDate) THEN

        LET FileCount = $(FileCount)+1;

        TRACE Loading $(File) ($(FileDate));

        MyTable:

        LOAD [Account No],

      DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')) as [Effective Date],

      [Tran Code],

      [Trans Amount]

      From $(File)

      (txt, codepage is 1252, embedded labels, delimiter is '~', no quotes, header is 1 lines)

WHERE ([Tran Code]=35 or [Tran Code]=36 or [Tran Code]=37 or [Tran Code]=931) and (exists([Account No]))

    ENDIF

NEXT File;

TRACE No more Files. $(FileCount) files loaded;

Please see the attached images as well - what am I doing wrong? It is the "Transactions" tab where I want to use this code (and eventually also on the applications tab).

[gerhardl]

Sorry, you might notice that I am loading something different now that my original post.

The Transactions (Tab 2 - see images), are just easier to start with, but eventually I want to apply the same logic for the Transaction Files that I load (all in one folder), as well as the Application Files (separate folders).

[#6]

Hi Gerhard,

Before the "Execution of script failed. Reload old data?" message, Qlikview pop-ups a message with a brief explanation about the error. Could you paste that text in order to make it easier to look for the error. The message should be something like this:

[gerhardl]

If I use the script I pasted above, then the Account Extract loads, and then it seems like one transaction extracts starts to load... it says this in the execution (only if I have "Close when finished" NOT ticket):

Loading C:\Users\tenba1\Documents\QlikView\Master File Extracts\Transaction Extracts (QVD)\Weekly_TransactionExtract_A_01-APR-2013_07-APR-2013_08042013050725.txt (2013/04/01)

But then the load stops and whern I click on CLose it gives me the "Execution of script failed. Reload old data?" message. See image 1.

Then I made this change:

LET FileDate     = Date(Date#(mid( '$(File)' ,29,11), 'DD-MMM-YYYY'));

And when I load I get this:

Script line error:

IF >2013/08/01 THEN

[stigchel]

It looks like you are evaluating the filename including the path where mid(file,29,11) does not evaluate to a valid date.

Maybe you can use something like the following (modify to your needs, the principle works):

DatesOfFiles:

LOAD MaxString(Mid(FileBaseName(),36, 4)&Mid(FileBaseName(),33, 3)&Mid(FileBaseName(),29, 2)) AS PossibleDates

FROM

*.txt

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

LET vLastDate = Peek('PossibleDates', -1);

LET file='Something'&vLastDate&'.txt';

DROP TABLE DatesOfFiles;

Data:

LOAD

....

FROM

$(file)

...

[gerhardl]

Hi Piet,

I only tried loading the DatesofFiles Table, and the concept works - the problem is this takes very long to load, which defeats my original purpose of speeding up the load by only loading from files that are relevant to the Campaign I am reporting on.

Any idea how I can get this statement right so that it sees a valid date:

LET FileDate     = Date(Date#(mid( '$(File)' ,29,11), 'DD-MMM-YYYY'));

An example of a filename is:

Weekly_ApplicationExtract_ACKS_05-AUG-2013_11-AUG-2013_12082013045043.txt

[#10]

Change the 29 for a 64. Thats the correct position where your date is. Beware with the date position in the filename, it must be always in the same place:

LET FileDate     = Date(Date#(mid( '$(File)' ,64,11), 'DD-MMM-YYYY'));