reduce data with latest dates

[userid128223]

Data:

Date	City	Rates
02/Jan/2016	Calgary	12;34;55;67
16/Aug/2016	Calgary	33;78;94;23
1/Dec/2015	Surrey	98;23;55;11;23;55;66
23/May/2015	Calgary	66;45;22;134
2/Feb/2015	Surrey	09;33;56;74;83
03/May/2018	Niagara Falls	334;22;6;343;5
17/July/2014	Niagara Falls	55;32;55;77

How can I restrict data during script execution (not in table) to only load rows which matches latest dates for each City and show corresponding Rates.

Results

Date	City	Rates
16/Aug/2016	Calgary	33;78;94;23
1/Dec/2015	Surrey	98;23;55;11;23;55;66
03/May/2018	Niagara Falls	334;22;6;343;5

[vishsaggi · Accepted solution]

This would help you:

Table1:

LOAD

     City,

     Rates,

     Date

FROM

[..\Desktop\LatestDates.xlsx]

(ooxml, embedded labels, table is Sheet1);

Filter:

INNER KEEP (Table1)

LOAD City,

     Date(Max(Date), 'DD-MMM-YYYY') AS Date

Resident Table1

Group by City;

DROP TABLE Filter;