Max value

[Dwyane]

Hello

I have a table that looks like this

Col1	Col2	Col3	Col4	Col5	Col6	Col7
AAA	1	100	01/01/2020	Context A	Context FGFB	Context GFC
AAA	2	300	01/01/2020	Context A	Context GFGB	Context DDC
AAA	3	0	01/01/2020	Context A	Context GFGB	Context ADC
AAA	4	300	01/01/2020	Context A	Context Z	Context AC

The output i am looking for the the last record so max col2 but only grouped by col1 and col4 with the additional columns so my output would be this

Col1	Col2	Col3	Col4	Col5	Col6	Col7
AAA	4	300	01/01/2020	Context A	Context Z	Context AC

I think i know how to do it in sql with a subquery but not sure how to so in Qlik script. Any help would be appreciated 

[Kushal_Chawda · Accepted solution]

@Dwyane  try below

Data:
LOAD Col1,
            Col2,
            Col3,
            Col4,
            Col5,
            Col6,
            Col7
FROM Table;

Left Join(Data)
LOAD Col1,
            Col4,
            max(Col2) as Col2,
1 as Flag
Resident Data
Group by Col1,
Col4;

Final:
NoConcatenate
LOAD *
Resident Data
where Flag=1;

DROP Table Data;

[Dwyane]

Thanks for now i would just like to build a table via script nothing on the front-end just yet

[Kushal_Chawda]

@Dwyane  what we have provided is the script solution only

[Dwyane]

Yes sorry i was responding to the other script that was posted which has been deleted