Max date in where clause - Load * where from qvd date=max(date)

[khigaurav999]

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

[Anil_Babu_Samineni]

You cannot use Aggregate function in Where statement. My option is this Set variable to maximum date of a qvd file load

[shiveshsingh]

[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];