Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
| 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 |
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;
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;