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: 
Anonymous
Not applicable

To extract folder and subfolder names

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

13 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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>     

                ...

          Next

          ...

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

          ...

     Next  

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

Thanks for your help..

Let me try ur logic and let you know

Thanks,

Leni

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

          NEXT

NEXT

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

jonathandienst
Partner - Champion III
Partner - Champion III

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

            NEXT

NEXT

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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