Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thanks for your help..
Let me try ur logic and let you know
Thanks,
Leni
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
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
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
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
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
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
I am getting the same vBaseDirName value in vSubFolder and vInnerFolder. I have done as you instructed only.