Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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];