Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a collection of records with dates. I want to select the highest day of each month. for example:
Article1 - 01-08-2013
Article1 - 10-08-2013
Article2 - 05-08-2013
Article2 - 14-08-2013
I want to select:
I want to do this in script. Something like MaxDay(), but MaxDate from the specific month.
add a
...
FirstSortedValue(SalesValue, -Date) as SalesValueAtMaxDate,
View solution in original post
Maybe like this
Table1:
LOAD Article, Date FROM ...;
Result:
Load Article,
Date(max(Date) as MaxDate
Resident Table1
Group By Article, monthstart(Date);
This does not work for me. I get a script error.
Seems that I missed a closing bracket for the Date() function.
After correcting this, works ok for me:
SET DateFormat = 'DD-MM-YYYY';
INPUT:
LOAD * INLINE [
Article, Date
Article1,01-08-2013
Article1,10-08-2013
Article2,05-08-2013
Article2,14-08-2013
Article1,01-07-2013
Article1,15-07-2013
Article2,05-07-2013
Article2,06-07-2013
];
RESULT:
Date(max(Date)) as MaxDate
Resident INPUT
Copy and paste this script
Final:
LOAD Article,
Date(max(Date),'DD-MM-YYYY')as maxDate
Group by Article;
SEE ATTACHED FILE
Thank you. Now this works. However, I have more columns connected to these articles. Lets say:
Article1 - 01-08-2013 - €10
Article1 - 10-08-2013 - €12
Article2 - 05-08-2013 - € 13
Article2 - 14-08-2013 - € 9
Article1 - 10-08-2013 - € 12
How can I put this in the provided code? The group by code seem to be troubling
try this
LOAD
LASTVALUE(Date) AS Date
FROM xyz.csv
GROUP BY Article, MONTHSTART(Date);
