Filter ListFilesAndFolders by LastWriteTime in a FTP connection

[Alonso-PSD]

Hi everybody!

I'm trying to filter the results of a ListFilesAndFolders of a FTP connection in QS using LastWriteTime field, but I couldn't find the way to do it.

My script is like:

let vLastDate = date(43594.3580439814,'DD/MM/YYYY hh:mm:ss tt');

FileList:
LOAD
    Name as File,
    LastWriteTime as Date
FROM
[http://localhost:5555/data?connectorID=FileTransferConnector&table=SFTPListFilesAndFolders&...]
(qvx) where LastWriteTime < $(vLastDate );

I tried some ways, converting LastWriteTime and vLastDate with date() and date#() but it was impossible for me to find the solution.

Could yo help me?

Thanks in advance.

[ashok_rajm]

Hi,
Try to load the data without filter and ensure that you use same format that is used in LastWriteTime field.
Or use
(qvx) where date(LastWriteTime,'DD/MM/YYYY hh:mm:ss tt') < $(vLastDate);

[Alonso-PSD]

Hi!

If I try your approach, the script execution enden with error: <Field 'a' not found>. But there isn't any call to 'a' field.

I think the problem is that ListFilesAndFolder FTP connector handles all files attributes (Name, LastWriteTime, Etc.) as text during the connection, so if I try to add a 'where' clause using date/time fields, it doesn't run (it return 0 results).

I try to filter the results of that FTP connection using 'where' clauses with Wildmatch() function and it worked fine, but it didn't do it with date/time functions.

Regards

Alonso.

[ashok_rajm]

Hi,
Yes you are right, If you load the qvx files, all the values are considered as text.

So try to change the where clause a bit:
where date(date#(LastWriteTime,'DD/MM/YYYY hh:mm:ss tt'),'DD/MM/YYYY hh:mm:ss tt') < '$(vLastDate)';

Use the variable in single cotes '$(vLastDate)'. 

With my current data i was able to achieve this.

If still you are getting same error, please post the sample application.