Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi, Mark. Try this
Left Join(Items)
Load
[Key],
[Main],
Max([Item Date]) as [Item Date],
'Y' as [Max Main]
Resident Items where [Main] = 'Y'
Group by [Key], [Main];
Hi, Mark. Try this
Left Join(Items)
Load
[Key],
[Main],
Max([Item Date]) as [Item Date],
'Y' as [Max Main]
Resident Items where [Main] = 'Y'
Group by [Key], [Main];