Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I would like to flag (e.g. add a new column) the latest date for each item ID/Country.
Example:
For Item ID=1 Country = FR, the item with date '2017-01-15' should be flagged as the latest.
For Item ID=1 Country = IT, the item with date '2017-03-18' should be flagged as the latest.
table:
Load Distinct * inline
[
Date, ID, Country, NBsales
'2015-12-20', 1, FR, 5
'2016-11-24', 1, FR, 5
'2016-12-01', 1, FR, 10
'2017-01-15', 1, FR, 20
'2016-11-25', 1, IT, 1
'2016-12-30', 1, IT, 8
'2017-01-15', 1, IT, 30
'2017-03-18', 1, IT, 28
'2016-11-24', 2, FR, 3
'2016-12-05', 2, FR, 1
'2017-01-20', 2, FR, 10
'2016-11-25', 2, IT, 5
'2016-12-12', 2, IT, 7
'2017-01-14', 2, IT, 45
'2017-03-18', 2, IT, 40
];
Many thanks for your help.
Annick
Hi annick,
It would be:
table:
Load *,date(date#(Date,'YYYY-MM-DD')) as DateField;
Load Distinct * inline
[
Date, ID, Country, NBsales
2015-12-20, 1, FR, 5
2016-11-24, 1, FR, 5
2016-12-01, 1, FR, 10
2017-01-15, 1, FR, 20
2016-11-25, 1, IT, 1
2016-12-30, 1, IT, 8
2017-01-15, 1, IT, 30
2017-03-18, 1, IT, 28
2016-11-24, 2, FR, 3
2016-12-05, 2, FR, 1
2017-01-20, 2, FR, 10
2016-11-25, 2, IT, 5
2016-12-12, 2, IT, 7
2017-01-14, 2, IT, 45
2017-03-18, 2, IT, 40
];
left Join(table)
load date(max(DateField)) as maxDate,ID,Country
Resident table
Group By ID,Country;
See the attached app
Hi annick,
It would be:
table:
Load *,date(date#(Date,'YYYY-MM-DD')) as DateField;
Load Distinct * inline
[
Date, ID, Country, NBsales
2015-12-20, 1, FR, 5
2016-11-24, 1, FR, 5
2016-12-01, 1, FR, 10
2017-01-15, 1, FR, 20
2016-11-25, 1, IT, 1
2016-12-30, 1, IT, 8
2017-01-15, 1, IT, 30
2017-03-18, 1, IT, 28
2016-11-24, 2, FR, 3
2016-12-05, 2, FR, 1
2017-01-20, 2, FR, 10
2016-11-25, 2, IT, 5
2016-12-12, 2, IT, 7
2017-01-14, 2, IT, 45
2017-03-18, 2, IT, 40
];
left Join(table)
load date(max(DateField)) as maxDate,ID,Country
Resident table
Group By ID,Country;
See the attached app
Thanks a lot Omar! it works well 🙂