Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks,
I need to do load where the report date (CC_ARReportingDate) is max.
[AR Data]:
LOAD DATA_FILE_CD,
AR_ID,
CARRIER_CODE,
CC_ARReportingDate,
%CC_MET_ID,
CC_TotalAR_SUM,
CC_TotalAR_SixtyDays_SUM,
CC_TotalAR_NinetyDays_SUM
FROM $(vRel_CBICARDataQVD)CC_AR_REPORT.QVD (qvd)
Where Max(CC_ARReportingDate);
I treid following optios as well:
// CC_ARReportingDate = Max(CC_ARReportingDate) - this doesn't work.
// Month(CC_ARReportingDate) = Month(Today()) - works but is not suitable for my scenario.
//CC_ARReportingDate >='12/31/2017' ; - works but is not suitable for my scenario and hard coded.
Any idea how to achieve this in a simple way without creating a complex scrip?
Thanks,
Gaurav
You cannot use Aggregate function in Where statement. My option is this Set variable to maximum date of a qvd file load
[AR Data]:
LOAD DATA_FILE_CD,
AR_ID,
CARRIER_CODE,
CC_ARReportingDate,
%CC_MET_ID,
CC_TotalAR_SUM,
CC_TotalAR_SixtyDays_SUM,
CC_TotalAR_NinetyDays_SUM
FROM $(vRel_CBICARDataQVD)CC_AR_REPORT.QVD (qvd)
T:Load max(CC_ARReportingDate) as Max_CC_ARReportingDate
Resident [AR Data];
Let vMAX = PEEK('Max_CC_ARReportingDate',0,'T');
Noconcatenate
Final:
Load *
Resident [AR Data]
where CC_ARReportingDate = '$(vMAX)';
drop table [AR Data];