Loading Dynamic excels into subfolders to store qvd

[Surya]

Hello,

I have folder structure 

Main folder

     2020 (sub folder)

          Jan(sub sub folder)

               Balance.xls

          Feb(sub sub folder)

                Balance.xls

         Nov(sub sub folder)

            Balance.xls

             Due.xls

    2021(sub folder)

        May(sub sub folder)

                Balance.xls

                Due. XLS

       Jun(sub sub folder)

               Balance.xls

Now I want to store qvd with balance.qvd and due.qvd

Need to Qvd contains monthyear flag

[stevejoyce]

This is a bit unclear...

You want to store qvds into your folder hierarchy dependent on date.  Like date of reload?

You can have variables in your store file path:

store table into [lib://folderconnection/subfolder/subfolder2/file.fixextension] (qvd);

So if you want to store qvds based on reload date, you can do this:

let vTodayYear = year(today());

let vTodayMonth = month(today());

<your load script>

store table into [lib://folderconnection/$(vTodayYear )/$(vTodayMonth )/file.qvd] (qvd);

[Surya]

Final qvd contain all balance.xls into single qvd

[Surya · Accepted solution]

SET vBaseDirName = 'lib://50_Data/Test/SC3/*';

FOR EACH vSubFolder IN DIRLIST(vBaseDirName)
SET vSFolder = $(vSubFolder)\*;

FOR EACH vInnerFolder IN DIRLIST(vSFolder)
Balance:
LOAD *,
FILENAME() AS NAME,
FILEDIR() AS NAME1,
DATE#(SUBFIELD(FILEDIR(),'/',-1) & SUBFIELD(FILEDIR(),'/',-2),'MMMYYYY') AS Date
FROM [$(vInnerFolder)/Balance.xlsx] (ooxml, embedded labels, table is Sheet1);

NEXT vInnerFolder
NEXT vSubFolder

STORE Balance INTO lib://50_Data/Test/SC3/Balance.qvd(QVD);