topic Re: To extract folder and subfolder names in QlikView

To extract folder and subfolder names

Anonymous — Mon, 01 Oct 2012 05:59:42 GMT

Hi,

I have requirement to extract the folder name and its subfolder name in Qlikview script.

The folder structure will be like below

Main Folder

SubFolder1 (the folder name will be date format. For eg., 20121001)

InnerSubFolder (the folder name will be in time format (24-hrs) appended by the name. For eg., 210125 - Invoice (HH:MM:SS))

...........

SubFolder2

.......

So in this till Main Folder the path is constant. I need to fetch the SubFolder1, SubFolder2,.... and InnerSubFolder,... paths dynamically in Qlikview script.

Under SubFolder we have many InnerSubFolders in that I need to take a particular folder based on the appended text as in above example I need to check for Invoice folder. Inside the 210125-Invoice folder I have text document and that text document will be the source file.

An Example:

To get the text document placed at *Invoice folder

Main Folder = c:\Sample\test\

SubFolder = 20120928, 20120929,20120930, 20121001

InnerFolder = 102345-Order,034546-Inventory,210125-Invoice

In this need to loop through all the SubFolders and inside those SubFolders need to check for the folders Invoice it can be prefixed with any time and need to use the text file inside those Invoice folders.

Thanks in Advance.

Thanks,

Leni Balakrishnan

Re: To extract folder and subfolder names

jonathandienst — Mon, 01 Oct 2012 06:24:41 GMT

Leni

I dont have a complete script for you,. but you can solve this with some nested "For Each .. in DirList()" loops:

For Each vSubfolder in DirList('c:\Sample\test')

...

<any logic using the subfolder name in vSubfolder can be performed here>

...

For Each vInnerSubfolder in DirList(vSubFolder)

...

<any logic using the inner subfolder name in vInnerSubfolder can be performed here>

...

...

<more logic using the subfolder name in vSubfolder can be performed here>

...

Hope that helps

Jonathan

Re: To extract folder and subfolder names

Anonymous — Mon, 01 Oct 2012 06:41:07 GMT

Thanks for your help..

Let me try ur logic and let you know

Thanks,

Leni

Re: To extract folder and subfolder names

Anonymous — Mon, 01 Oct 2012 07:00:01 GMT

Use the following in your load :-

LOAD

MakeDate(mid(SubField(filepath( ),'\',4),1,4), mid(SubField(filepath( ),'\',4),5,2), mid(SubField(filepath( ),'\',4),7,2)) as DATE,
Maketime(mid(SubField(filepath( ),'\',5),1,2), mid(SubField(filepath( ),'\',5),3,2), mid(SubField(filepath( ),'\',5),5,2)) as Time,

Note:- for SubField(filepath( ),'\',4)

the value depends on the constant path

e.g:- c:\Sample\test\ 20120928\ 102345-Order.txt

use SubField(filepath( ),'\',4) for date

SubField(filepath( ),'\',5) for time

change in case directory changes

Re: To extract folder and subfolder names

Anonymous — Mon, 01 Oct 2012 11:55:44 GMT

Hi Shekharnil,

Thanks for your reply.

But my requirement is to get the subFolders and the innerfolders name to frame the whole path.

Thanks,

Leni

Re: To extract folder and subfolder names

Anonymous — Mon, 01 Oct 2012 12:07:14 GMT

Hi Jonathan,

I have tried the loop structure you have said above

set vBaseDirName = 'D:\Sample\Test\';

FOR Each vSubFolder in DirList($(vBaseDirName))

FOR Each vInnerFolder in DirList(vSubFolder)

if(WildMatch($(vInnerFolder),'*Invoice') >0)

Tb1:

LOAD

FROM

[$(vBaseDirName)\$(vSubFolder)\$(vInnerFolder)\file1.txt]

(txt, utf8, no labels, delimiter is '\t', msq);

ENDIF

When I tried to load this script into Qlikview , The value of vSubFolder, vInnerFolder are same as vBaseDirName.

I am not sure why the value is coming like tat.

Please guide me in this.

Thanks,

Leni

Re: To extract folder and subfolder names

jonathandienst — Tue, 02 Oct 2012 11:18:06 GMT

Leni

If you use a $() expansion, you need to also include quotes when used as a parameter. Or just remove the unneeded variable expansions:

FOR Each vSubFolder in DirList(vBaseDirName)

TRACE vSubFolder = $(vSubFolder);

FOR Each vInnerFolder in DirList(vSubFolder)

TRACE vInnerFolder = $(vInnerFolder);

if(WildMatch(vInnerFolder,'*Invoice') >0)

Tb1:

LOAD

FROM [$(vInnerFolder)\file1.txt]

(txt, utf8, no labels, delimiter is '\t', msq);

ENDIF

Also, I think vInnerFolder contains the complete folder path, so I have amended the FROM clause. The TRACE statements will help with debugging and can be deleted once the script is working

Regards

Jonathan

Re: To extract folder and subfolder names

rwunderlich — Tue, 02 Oct 2012 19:49:32 GMT

Instead of coding up your own dirlist loop, you may want to consider using the Qlikview Components script library http://qlikviewcomponents.org. Using the Qvc, your complete code would look like this:

$(Include=..\qvc_root\qvc\qvc_runtime\qvc.qvs);

SUB LoadInvoice (file)

IF '$(file)' LIKE '*\*-Invoice\*' THEN

LOAD * // Your load statement

FROM

'$(file)'

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

ENDIF

END SUB

CALL Qvc.ListFiles('c:\temp\MainFolder', '*', -1, 'LoadInvoice');

-Rob

http://robwunderlich.com

Re: To extract folder and subfolder names

Anonymous — Wed, 03 Oct 2012 08:41:19 GMT

This should do :-

set vBaseDirName = 'D:\Sample\Test\*';

FOR Each vSubFolder in DirList(vBaseDirName)
SET vSFolder = $(vSubFolder)\*;

FOR Each vInnerFolder in DirList(vSFolder)

if WildMatch(vInnerFolder,'*Invoice')>0 then
tb:
LOAD
*
FROM [$(vInnerFolder)\file.txt]
(txt, utf8, no labels, delimiter is '\t', msq);

ENDIF
NEXT vInnerFolder
NEXT vSubFolder

Re: To extract folder and subfolder names

Anonymous — Wed, 03 Oct 2012 14:01:12 GMT

I am getting the same vBaseDirName value in vSubFolder and vInnerFolder. I have done as you instructed only.

Re: To extract folder and subfolder names

Anonymous — Wed, 03 Oct 2012 14:07:58 GMT

Hi Rob,

Thanks for your answer

I have added the above lines @ script and when I tried to load, I am getting script line error

What the Include statement has?? How can I add the same in my application.

Could you explain the above code??

Regards,

Leni

Re: To extract folder and subfolder names

rwunderlich — Wed, 03 Oct 2012 14:50:50 GMT

The include statement includes the Qvc runtime script. You can download it at the url given above.

-Rob

Re: To extract folder and subfolder names

Anonymous — Fri, 05 Oct 2012 06:03:45 GMT

Hi,

Thanks.

Using your above logic I got the full path of the file.

Thanks a lot

Thanks,

Leni

Re: To extract folder and subfolder names

rlp — Fri, 05 Oct 2012 07:42:42 GMT

You can use negative arguments with Subfield so that Subfield( <string> , <delimiter> -1 ) gives you the last part of the string. You can so obtain the filename with Subfield( _ , '\' , -1 ) and the subfolder with -2.