HI all,
I'm dumping an excel file every week on Saturday. These files will have this naming convention: "ALL_OPEN_AR_YYYYMMDD"
This means that there will be 4 files a month that I'm looking to incrementally load and combine into one QVD. It appears that my script is looking for the specific dates and when the file name isn't there, it errors and stops loading.
I'm hoping I can tell it to grab all files within vFromDate and vToDate
or tell it to skip 6 days for each load date.
(Must_Include=[lib://Revenue Cycle Dev/ExternalData/Includes\Rev_Cycle_QVD_Builder2_Include.txt]);
LET external_tablename = Subfield(SubField(DocumentTitle(),'_L1_',1),'XLS.', -1);
LET _fwFullQVDName = '$(EXT_L1_Path)XLS.$(external_tablename).qvd';
Let vFromDate = Date(MonthName(AddMonths(Today(),-12)),'YYYYMMDD');
Let vToDate = Date(Today(),'YYYYMMDD');
Set vQVDLibraryAndFolder = 'lib://Revenue Cycle Dev/ExternalData\';
Set vQVDFileNameWithoutDateSuffix = 'ALL_OPEN_AR';
TempPartitionCalendar:
Load Distinct
Date((TempDate), 'YYYYMMDD') As PartitionYearMonth;
Load
Date(minDate + IterNo()) As TempDate
While minDate + IterNo() <= maxDate;
Load
Date#('$(vFromDate)','YYYYMMDD') as minDate,
Date#('$(vToDate)','YYYYMMDD') as maxDate
AUTOGENERATE (1);
For vIndex = 1 to FieldValueCount('PartitionYearMonth')
Let vYearMonthSuffix = FieldValue('PartitionYearMonth',$(vIndex));
Let vQVDSource = '$(vQVDLibraryAndFolder)$(vQVDFileNameWithoutDateSuffix)_$(vYearMonthSuffix).xlsx';
//****************************************************************************************************
// Load Data Source Data
LET execution_start = Now();
$(external_tablename):
LOAD [Patient Name],
[HAR],
[Reposted No New Claim],
[Last Invoice #],
[Dept ID],
[Dept Name],
[CPT Code],
[Ins Bal],
[AR Amount],
[Current Payer Name],
[Current Plan Name],
[FOLLOW UP WQ ID],
[FOLLOW UP WQ NAME]
FROM [$(vQVDSource)]
(ooxml, embedded labels, table is Sheet1);
LOAD [Patient Name],
[HAR],
[Reposted No New Claim],
[Last Invoice #],
[Dept ID],
[Dept Name],
[CPT Code],
[Ins Bal],
[AR Amount],
[Current Payer Name],
[Current Plan Name],
[FOLLOW UP WQ ID],
[FOLLOW UP WQ NAME]
FROM [$(vQVDSource)]
(ooxml, embedded labels, table is Sheet2);
//****************************************************************************************************
// Stores Data Source into a QVD File
LET _fwWriteQVDStartDT = Now();
STORE [$(external_tablename)] INTO [$(_fwFullQVDName)] (qvd);
DROP TABLE [$(external_tablename)];
DROP TABLE [TempPartitionCalendar];
LET _fwLastRun = Today();
//****************************************************************************************************
Exit Script;
