Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
JohnSamuel123
Contributor III
Contributor III

QlikView mapping to get most recent record

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?

 

1 Solution

Accepted Solutions
JohnSamuel123
Contributor III
Contributor III
Author

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

 

View solution in original post

2 Replies
stevejoyce
Specialist II
Specialist II

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
Contributor III
Contributor III
Author

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