topic QlikView mapping to get most recent record in QlikView

QlikView mapping to get most recent record

JohnSamuel123 — Tue, 21 Sep 2021 13:21:32 GMT

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?

Re: QlikView mapping to get most recent record

stevejoyce — Tue, 21 Sep 2021 13:29:58 GMT

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

Re: QlikView mapping to get most recent record

JohnSamuel123 — Tue, 21 Sep 2021 14:04:46 GMT

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