Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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!
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...
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...
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
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"
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................
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;
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
Attached is the log file but doubt that is much help.