Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@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 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;
Thanks for now i would just like to build a table via script nothing on the front-end just yet
@Dwyane what we have provided is the script solution only
Yes sorry i was responding to the other script that was posted which has been deleted