Flag for max values in load

[Anonymous]

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?

[andrey_krylov · Accepted solution]

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];