script advise

[#1]

Hi All,

I want to load my text files incrementally.

Files like File18082016.txt,File19082016.txt,File20082016.txt,..

Delete script is not working.

Please correct the script.

Let ThisExecTime = Now( );
QV_Table:
LOAD *,FileName() as FileName,
FileTime() as FileTime
FROM
[..\Source Files\File*.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
WHERE FileTime() >= '$(LastExecTime)'
AND FileTime() < '$(ThisExecTime)';
Concatenate
LOAD *
FROM
[QV_Table.qvd]
(qvd)
WHERE NOT EXISTS(FileName);
//Inner Join
//LOAD FileTime() as FileTime
//FROM
//[..\Source Files\File*.txt]
//(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
If ScriptErrorCount = 0 then
STORE QV_Table INTO QV_Table.qvd(qvd);
Let LastExecTime =ThisExecTime;
End If
EXIT Script;

[Gysbert_Wassenaar]

Perhaps these works:

Let ThisExecTime = num(Now());

QV_Table:
LOAD *,FileName() as FileName,
FileTime() as FileTime
FROM
[..\Source Files\File*.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
WHERE num(FileTime()) >= $(LastExecTime)
AND num(FileTime()) < $(ThisExecTime);

[#3]

Hi Gysbert,

How this will work =$(LastExecTime)? As we didn't have declare the variable?

[Gysbert_Wassenaar]

You declare it at the end of the script. That's when it gets a value. That variable will still exist and have that value the next time you execute the script.

[#5]

Hi Gysbert,

Seems this is not working.

Please test with the attached files.

Let ThisExecTime = num(Now( ));
QV_Table:
LOAD *,FileName() as FileName,
FileTime() as FileTime
FROM
[..\Source Files\File*.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
WHERE num(FileTime()) >= '$(LastExecTime)'
AND num(FileTime()) < '$(ThisExecTime)';
Let LastExecTime =num(ThisExecTime); 

[Gysbert_Wassenaar]

Remove the single quotes in the where clause.

[#7]

I have done but still the data is not loading. Please help me full script.

[Gysbert_Wassenaar]

Let ThisExecTime = num(Now());

QV_Table:

LOAD

  *,

  FileName() as FileName,

  FileTime() as FileTime

FROM

  [File*.txt]

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

WHERE

  num(FileTime()) >= $(LastExecTime)

  AND num(FileTime()) < $(ThisExecTime)

  ;

Let LastExecTime = num(ThisExecTime);