Flag for max values in load

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I have this table called Items:

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?

Re: Flag for max values in load

andrey_krylov — Wed, 05 Sep 2018 04:33:04 GMT

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