Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Currently i got multiple records in database. How can I create expression to retrieve data with specific Year and Max Date.
Sample Data:
LOAD * INLINE [
Record, Date, Year
abc, 7/9/2015, 2015
abc, 6/8/2015, 2015
abc, 4/1/2015, 2015
abc, 12/3/2016, 2016
abc, 16/2/2016, 2016
abc, 6/1/2016, 2016
abc, 1/1/2017, 2017
abc, 6/2/2017, 2017
];
I only able to get the required Year but not the max date:
My Expression:
=count({$<Year={'$(=max(Year)-1)'}>} Record)
My Result:
My Expectation Result:
Your problem is around date formats. The dates are loading as 'd/M/yyyy', but your environment setting is probably 'dd/MM/yyyy'. Try:
SET DateFormat='dd/MM/YYYY';
=Count({<Date = {"$(=Date(Max({<Year={'$(=Max(Year)-1)'}>} Date), 'd/M/yyyy'))"}>} Record)
or
SET DateFormat='d/M/YYYY';
=Count({<Date = {"$(=Date(Max({<Year={'$(=Max(Year)-1)'}>} Date)))"}>} Record)
Hi,
Please try below expression
=count({$<Year={'$(=max(Year)-1)'},Date={'$(=max(Date))'}>} Record)
Hi dada khalander,
Thanks but the formula not work. I got zero (0) result
Hello Haniff,
As a workaround I have created script level solution for this problem. Created a Resident Load to pull maximum date for each Record and Year (refer below given sample script).
Data:
LOAD * INLINE [
Record, Date, Year
abc, 7/9/2015, 2015
abc, 6/8/2015, 2015
abc, 4/1/2015, 2015
abc, 12/3/2016, 2016
abc, 16/2/2016, 2016
abc, 6/1/2016, 2016
abc, 1/1/2017, 2017
abc, 6/2/2017, 2017
];
NoConcatenate
NewData:
LOAD
Record AS NewRecord,
Year AS NewYear,
Date(Max(Date),'MM/DD/YYYY') AS NewDate
Resident Data
Group By Record, Year;
Regards!
Rahul
Hi Rahul,
Sorry. I use QV Personal Edition and cannot open your attachment.
By using the script provided by you, now I got 2 tables and the data become bigger. Can you help me to copy the expression script from your sample?
Regards.
Your problem is around date formats. The dates are loading as 'd/M/yyyy', but your environment setting is probably 'dd/MM/yyyy'. Try:
SET DateFormat='dd/MM/YYYY';
=Count({<Date = {"$(=Date(Max({<Year={'$(=Max(Year)-1)'}>} Date), 'd/M/yyyy'))"}>} Record)
or
SET DateFormat='d/M/YYYY';
=Count({<Date = {"$(=Date(Max({<Year={'$(=Max(Year)-1)'}>} Date)))"}>} Record)
Thanks Jonathan for your solution.