topic Loading Dynamic excels into subfolders to store qvd in App Development

Loading Dynamic excels into subfolders to store qvd

Surya — Tue, 30 Nov 2021 16:25:32 GMT

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

Re: Loading Dynamic excels into subfolders to store qvd

stevejoyce — Thu, 26 Aug 2021 17:45:59 GMT

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());

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

Re: Loading Dynamic excels into subfolders to store qvd

Surya — Fri, 27 Aug 2021 01:21:15 GMT

Final qvd contain all balance.xls into single qvd

Re: Loading Dynamic excels into subfolders to store qvd

Surya — Wed, 01 Sep 2021 11:22:57 GMT

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);