Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

markramiro
Contributor II

Flag for max values in load

Hi Experts,

I have this table called Items:

KeyItem DateMain
AAA-1231/1/2018
AAA-1236/1/2020Y
AAA-1235/1/2019Y
AAA-1235/1/2019
BBB-5551/1/2017
BBB-5551/1/2017Y
BBB-5555/1/2018Y
BBB-5553/1/2020

Trying to add a new field, [Max Main], like so:

KeyItem DateMainMax Main
AAA-1231/1/2018
AAA-1236/1/2020YY
AAA-1235/1/2019Y
AAA-1235/1/2019
BBB-5551/1/2017
BBB-5551/1/2017Y
BBB-5555/1/2018YY
BBB-5553/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?

1 Solution

Accepted Solutions
Highlighted
andrey_krylov
Valued Contributor

Re: Flag for max values in load

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

View solution in original post

1 Reply
Highlighted
andrey_krylov
Valued Contributor

Re: Flag for max values in load

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

View solution in original post