topic Re: Load file based on timestamp in QlikView

Load file based on timestamp

gerhardl — Tue, 20 Aug 2013 11:17:14 GMT

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

Re: Load file based on timestamp

Tue, 20 Aug 2013 13:44:29 GMT

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.

Re: Load file based on timestamp

pgrenier — Tue, 20 Aug 2013 13:56:55 GMT

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 😉

Re: Re: Load file based on timestamp

gerhardl — Wed, 21 Aug 2013 08:55:25 GMT

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

Re: Re: Load file based on timestamp

gerhardl — Wed, 21 Aug 2013 09:05:23 GMT

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

Re: Re: Re: Load file based on timestamp

Wed, 21 Aug 2013 09:16:54 GMT

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:

Re: Re: Re: Re: Load file based on timestamp

gerhardl — Wed, 21 Aug 2013 09:36:39 GMT

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

Re: Load file based on timestamp

stigchel — Wed, 21 Aug 2013 09:53:25 GMT

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)

...

Re: Re: Re: Re: Load file based on timestamp

gerhardl — Wed, 21 Aug 2013 10:02:08 GMT

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

Re: Load file based on timestamp

Wed, 21 Aug 2013 10:19:34 GMT

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

Re: Load file based on timestamp

stigchel — Wed, 21 Aug 2013 10:24:19 GMT

the path is included in S(File) so the position of the date is not 29, find the correct position including the path or remove it first. Should be something like =MID(S(File),115,11)

Re: Re: Re: Re: Load file based on timestamp

gerhardl — Wed, 21 Aug 2013 10:24:51 GMT

Hi Philippe,

Thanks for your proposal. As I mentioned in my response to Piet, loading all of the filenames first will defeat my original purpose of speeding up the load.

I thought there would be a way of just manipulating the FROM statement so that it looks at a variable.

So I would often say something like:

SET vYear = 2013;

LOAD *

FROM [directory\*$(vYear)*.txt]

And this will then just load files where the name contains "2013". Of course this is purely based on text - I expected there would be an easy way to change this so that it recognises the datestamp... but I am really struggling!

Re: Re: Re: Re: Re: Load file based on timestamp

gerhardl — Wed, 21 Aug 2013 10:42:57 GMT

The corerct position, including the path seemd to be 112.

Still it doesn't work. It loads, and the fails with no more error messages. In brackets it does show the date based on the fiel name now:

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)

This is going to drive me insane...

Re: Load file based on timestamp

stigchel — Wed, 21 Aug 2013 11:58:11 GMT

Strange that a date of 2013-04-01 should evaluate > MinFileDate which is today-10 ??? or Am i missing something?

Other then that it seems that the datefile part of the script is now working and that you maybe have an other problem not related to this? My first hunch would be the exists condition...

Re: Re: Re: Re: Re: Load file based on timestamp

gerhardl — Wed, 21 Aug 2013 13:26:42 GMT

If it was the exists condition it should just have loaded zero records, not bombed out, right? But to test I took that out and it still did the same.

I tried setting the MinFileDate to an older date so that 2013/04/01 WOULD actually be > than it, and then this happens:

Loading C:\Users\tenba1\Documents\QlikView\Master File Extracts\Transaction Extracts (QVD)\Weekly_TransactionExtract_A_02-JAN-2012_08-JAN-2012_09012012033516.txt (2012/01/02)

--- Script Finished ---

And it also bombs out with the error message... but it tried to load the second file in the list.

So that tells me it goes through the files (but it does not actually load anything), until it reaches a file where the date is smaller than MinFileDate then it bombs

Re: Load file based on timestamp

Brice-SACCUCCI — Wed, 21 Aug 2013 14:05:13 GMT

Hi,

Philippe suggest to load the filenames, not the files, which should not take that long. You should give it a try I think.

You could replace the condition in the IF statement with whatever you want to do, like index(vFileName, '2013') > 0, to only keep files containing "2013"

Re: Load file based on timestamp

stigchel — Wed, 21 Aug 2013 14:21:07 GMT

I did some testing and I did miss something, the statement

IF $(FileDate)>$(MinFileDate)

is NOT evaluating as dates.

I solved this in my test by storing the Num of Date in the variables, so in your case:

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

and

LET FileDate=Num(Date#(Mid................

Re: Re: Load file based on timestamp

gerhardl — Wed, 21 Aug 2013 14:36:03 GMT

Hi,

You are correct, it does load quickly - sorry about that. But I can't get it to load the transactions, even though it reads all of the filenames correctly. It gives no error - it just loads nothing.

Please see if you can see a problem here, or in the images:

LET vRoot = 'C:\Users\tenba1\Documents\QlikView\Master File Extracts\Transaction Extracts (QVD)';

LET vDateFrom = Date(Today()-20);

LET vDateTo = Date(Today());

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, '_', 4), 'DD-MMM-YYYY')) <= vDateTo then

TranExtracts:

LOAD [Account No],

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

[Tran Code],

[Trans Amount]

From [$(vRoot)\$(vFileName)]

(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 i;

Re: Re: Load file based on timestamp

gerhardl — Wed, 21 Aug 2013 14:39:43 GMT

I get the same result as before except the date in brackets shows as Num:

Loading C:\Users\tenba1\Documents\QlikView\Master File Extracts\Transaction Extracts (QVD)\Weekly_TransactionExtract_A_12-AUG-2013_18-AUG-2013_19082013093040.txt (41498)

And then the error

Re: Re: Load file based on timestamp

gerhardl — Wed, 21 Aug 2013 14:58:30 GMT

Attached is the log file but doubt that is much help.