topic Re: FileName in SubDirectories in QlikView

FileName in SubDirectories

lylererger — Fri, 23 Aug 2013 08:00:31 GMT

Hi Friends. Help me find the right way to get filename.
I'm doing the following:

Sub DoDir (Root)

For Each Ext In 'qvd' // filetype to search for in current directory

For Each File In FileList (Root & '\*.' & Ext)

Files:

Load '$(File)' as WayToFile,

FileTime('$(File)') as FileTime,

Replace(SubField('$(File)','\',4),'.qvd','') as FileName

Autogenerate 1;

Next File

Next Ext

For Each Dir In DirList (Root & '\*') // searching in subdirs

Call DoDir (Dir)

Next Dir

End Sub

Call DoDir ('..\incrementalload'); // Starting point

In Root folder all is OK. But when i'm trying to get filename in SubDirectories all fall.I mean that the following construction

Replace(SubField('$(File)','\',4),'.qvd','') as FileName is not right for subdirs.

Please help me solve that moment!

Re: FileName in SubDirectories

marcus_sommer — Fri, 23 Aug 2013 08:05:58 GMT

There are another file-functions like filename(), filebasename() and so on - see by file-functions.

- Marcus

Re: FileName in SubDirectories

MayilVahanan — Fri, 23 Aug 2013 08:20:02 GMT

Try like this

For Each File In FileList (Root & '\*.' & Ext)

Files:

Load *, Replace(SubField(WayToFile,'\',4),'.qvd','') as FileName;

Load '$(File)' as WayToFile,

FileTime('$(File)') as FileTime

Autogenerate 1;

Next File

Re: FileName in SubDirectories

CELAMBARASAN — Fri, 23 Aug 2013 08:32:56 GMT

Hi,

Try with the below script

Sub DoDir (Root)

For Each Ext In 'qvd' // filetype to search for in current directory

For Each File In FileList (Root & '\*.' & Ext)

Files:

Load '$(File)' as WayToFile,

FileTime('$(File)') as FileTime,

Replace(Mid('$(File)',Index('$(File)','\', SubStringCount('$(File)','\'))+1),'.$(Ext)','') AS FileName,

Autogenerate 1;

Next File

Next Ext

For Each Dir In DirList (Root & '\*') // searching in subdirs

Call DoDir (Dir)

Next Dir

End Sub

Hope it helps

Re: FileName in SubDirectories

Deepak_dhikale — Thu, 19 Aug 2021 08:12:17 GMT

Hi @MayilVahanan

I want to separate the data load script based on filename

FilesAndFolders:
LOAD
Name,
Path,
IsDirectory,
Size,
SubField(SubField(Name,' ',1),'_',2) as newfile

FROM [lib://sftp_url (allenlund_nprinting)]

(URL is [http://qliksense:5555/data?connectorID=FileTransferConnector&table=SFTPListFilesAndFolders&host=harmony-la.allenlund.com&port=5022&userName=QlikServer&password=sta_gen_1%3aekhHROY5hOEHHqLj%2fHgFaQ%3d%3d%3aQ5a0YHe5UOEN%2fkNHJBVKuw%3d%3d&ignoreProxy=False&ignoreClientCertificateErrors=False&useLegacyGroupExchange=False&remoteDirectory=%2fhome%2fQlikServer%2foutbox%2f*.csv*&appID=],qvx);

LET vNoOfFiles = NoOfRows('FilesAndFolders');

with the above script i get below output

i just want to make separate load script for weekly_3_day_rates and weekly_7_day_rates .as all tables contains same no of rows and coulumns.

thank you 🙂

Re: FileName in SubDirectories

MayilVahanan — Thu, 19 Aug 2021 09:50:31 GMT

Are you want to load only weekly files? If so, try like below

LOAD
Name,
Path,
IsDirectory,
Size,
SubField(SubField(Name,' ',1),'_',2) as newfile

FROM [lib://sftp_url (allenlund_nprinting)]

(URL is [http://qliksense:5555/data?connectorID=FileTransferConnector&table=SFTPListFilesAndFolders&host=harm...)

Where Wildmatch(Name, 'week*')=-1;