Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Alonso-PSD
Contributor
Contributor

Filter ListFilesAndFolders by LastWriteTime in a FTP connection

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.

Labels (3)
3 Replies
ashok_rajm
Contributor III
Contributor III

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
Contributor
Contributor
Author

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
Contributor III
Contributor III

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.