Hi Experts,
I have this table called Items:
| Key | Item Date | Main |
|---|
| AAA-123 | 1/1/2018 | |
| AAA-123 | 6/1/2020 | Y |
| AAA-123 | 5/1/2019 | Y |
| AAA-123 | 5/1/2019 | |
| BBB-555 | 1/1/2017 | |
| BBB-555 | 1/1/2017 | Y |
| BBB-555 | 5/1/2018 | Y |
| BBB-555 | 3/1/2020 | |
Trying to add a new field, [Max Main], like so:
| Key | Item Date | Main | Max Main |
|---|
| AAA-123 | 1/1/2018 | | |
| AAA-123 | 6/1/2020 | Y | Y |
| AAA-123 | 5/1/2019 | Y | |
| AAA-123 | 5/1/2019 | | |
| BBB-555 | 1/1/2017 | | |
| BBB-555 | 1/1/2017 | Y | |
| BBB-555 | 5/1/2018 | Y | Y |
| BBB-555 | 3/1/2020 | | |
Logic for Max Main=Y is basically:
Main = 'Y'
Date = Max(Item Date)
For each Key
Was trying to add this to the load script -
Left Join(Items)
Load
[Key],
'Y' as [Max Main]
Resident Items where [Item Date]=Max([Item Date]) and [Main]='Y'
Group by [Key];
But definitely wrong as getting an error in the load. Any help?
