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

[stigchel]

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)

[gerhardl]

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!

[gerhardl]

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

[stigchel]

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

[gerhardl]

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

[Brice-SACCUCCI]

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"

[stigchel]

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

[gerhardl]

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;

[gerhardl]

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

[gerhardl]

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