Flag items with the latest date in a table, adding a column

[#1]

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

[OmarBenSalem · Accepted solution]

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

[#3]

Thanks a lot Omar! it works well 🙂