I've been trying to look for the differences but cant seem to find it. is there a way to specify that if balanceCurrent, balanceFuture, postingDate and DocumentDate matches remove the row? would that be my best approach? or would the problem be with my calendar?
APPeriodSummary:
LOAD Distinct
balanceCurrent,
balanceFuture,
current,
futureBalance,
invoices,
oid as myAPPeriodSummary,
payments,
purchases,
totalBalance
FROM
[..\APPeriodSummary.qvd](qvd);
APInvoice:
LOAD distinct
postingDate,
lcNetAmount,
oid as myAPInvoice,
paymentDate,
documentDate,
reference
FROM
[..\APInvoice.qvd](qvd);
Temp:
Load
min(postingDate) as minDate,
max(postingDate) as maxDate
Resident APInvoice;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
Load distinct
Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year
Dual(Month, fMonth) as FMonth, // Dual fiscal month
Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,
Ceil((postingDate-StartOfFWeekOne+1)/7) as FWeekNo,
*;
Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month
Dual('Q' & Ceil(Month/3), Ceil(Month/3)) as Quarter,
WeekStart(FYearStart,0,$(vFD)) as StartOfFWeekOne,
*;
MasterCalendar:
Load
TempDate AS postingDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
YearStart(TempDate,0,$(vFM)) as FYearStart,
Week(TempDate) & '-' & Year(TempDate) as WeekYear,
Dual(WeekDay(TempDate),Mod(WeekDay(TempDate-$(vFD)),7)+1) as WeekDay,
Date(TempDate,'MM/DD') as DATEMMDD
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
thanks.
Bon.
