Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
khigaurav999
Creator
Creator

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

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

2 Replies
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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
shiveshsingh
Master
Master

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