QlikView mapping to get most recent record

[JohnSamuel123]

hi,

im trying to create a flag that will store the most recent record for a specific field.

i am using the"unique record ID(f_sgrp) as my max, then just grouping it by LINEID. however im dealing with an oracle SQL database and it doesn't seem to allow me to do this.

my mapping is a simple:

mapping
load f_prcs, //this is my lineID
max(f_SGRP) //this is my unique record ID
;
sql select f_prcs, f_sgrp

from X
WHERE Y
group by f_prcs

in my log file it is saying 

"Error: Connector reply error: SQL##f - SqlState: S1000, ErrorCode: 979, ErrorMsg: [Oracle][ODBC][Ora]ORA-00979: not a GROUP BY expression" i think this is because for oracle SQL you have to group by both select statements?

however when i try that, i get the error message:

Error: Field 'f_SGRP' not found

any ideas on how to correct this?

[stevejoyce]

Your group by should be in the same select statement of your aggregation.  What if you try:

mapping
load f_prcs, //this is my lineID
max(f_SGRP) //this is my unique record ID

group by f_prcs
;
sql select f_prcs, f_sgrp

from X
WHERE Y

[JohnSamuel123 · Accepted solution]

hi @stevejoyce , thanks for that, 

i got it working by using:

mapping
sql select f_prcs, max(f_sgrp) from X
where
Y
group by f_prcs