13 Replies Latest reply: Oct 5, 2012 3:42 AM by Richard Pressanti RSS

    To extract folder and subfolder names

    Leni Balakrishnan

      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
          Jonathan Dienst

          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

            • Re: To extract folder and subfolder names
              Leni Balakrishnan

              Thanks for your help..

               

              Let me try ur logic and let you know

               

              Thanks,

              Leni

              • Re: To extract folder and subfolder names
                Leni Balakrishnan

                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

                  • Re: To extract folder and subfolder names
                    Jonathan Dienst

                    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

                    • Re: To extract folder and subfolder names
                      Rob Wunderlich

                      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
                    Nilendu Shekhar Ghosh

                    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