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]

Ah, you have a general script error...This may not be related to any part of your script, but for example files being locked \ network errors .....

I would suggest you do the following:

- restart your computer completely try to run it again

- if this fails also, try creating a 'test' environment where you copy one of all the different files needed in one directory including the qvw file. Try to exclude any possible reason for external error to narrow down the possible reason for error (e.g. I would not use C:\users\ kind of directories)

[gerhardl]

Okay, some progress!

So i restarted my PC and it did not help. Then I set up a test environment, pulling the data from a new folder on my C Drive. it works so nicely I could cry.

But now I know the script is oka... but what do I do about this? I cannot move all of the files lying in the user folders. There are too many other tasks and QlikView files that uses them as is.

Any ideas?

thanks so much for all the help so far.

[stigchel]

Maybe you can try to work your way back to the original situation, step by step and see if and when the error reoccurs. It may be related to a single file or directory (access rights??)

Good luck in finding the error, I think I can't help you any further than this...

[gerhardl]

Thanks you so much for all your help. Hopefully I can figure it out.

I think it might have to do with spaces in the path, because I created a separate folder now, also not in the users folder and it failed. But in a folder where the path has no spaces it works. Tried adding double-quotes to the path but didn't work... will keep playing around.

Thanks again.

I'm going to mark as correct the post where this script was first given, since the problem was not with the script at all it seems. Here is my final scrip for anyone who wants it in the future:

// Variable for counting the number of files loaded:

LET FileCount    = 0;

// Variable to set the minimun file date:

LET MinFileDate    = Num('2013/08/01');

FOR Each File in FileList ('C:\QA\*.txt');

    LET FileDate     = Num(Date(Date#(mid( '$(File)',35,11), 'DD-MMM-YYYY')));    //Remember that this START point (89) depends on the path of the files

    IF $(FileDate)>$(CampaignStartDate) 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;

[stigchel]

If so then try using [], so:

     From [$(File)]