I have attached an example file using your example. There is a limitation to using this. The systemIDs will have to be sorted in ascending order and the overideId has to be less the systemid. If these conditions are met in your data model, then it will work.
PS. By mistake, I pressed the suggested answer to your question.
You can left join the table onto itself to establish an OverRideScore, then do it one more time to establish a FinalScore. Report the FinalScore. Drop the other scores if you don't need them, or leave them if you need the raw information.
LEFT JOIN (Data) LOAD SystemID as OverRideID ,SCORE as OverRideScore RESIDENT Data ; LEFT JOIN (Data) LOAD SystemID ,alt(OverRideScore,SCORE) as FinalScore RESIDENT Data ;
A second approach is to use a combination of fieldvalue() and fieldindex() to look up the correct score in the chart. Technique courtesy of Peter Rieper in a post yesterday.
Nimish Shah wrote:Is this expensive (especially over large data sets?). Also, are the double quotes required for the fieldvalue pararmeter in the fieldindex?
Honestly, I'm not sure how expensive those options are. Two left joins in a row will take some time on the load, and the bigger the table, the worse it would be. But then the chart itself is just reporting existing fields, so that part should be just fine. And I haven't done any experimentation on fieldvalue() and fieldindex() speed. My GUESS is that they'll be very fast. My GUESS is that QlikView stores the fieldindex rather than recomputing it on the fly, in which case the chart expression should be very quick, and shouldn't degrade much on large data sets.
The single and double quotes should be as I showed them. From the help text:
"FieldValue( fieldname , n ) Returns the field value found in position n of the field fieldname (by load order). fieldname must be given as a string value, e.g. the field name must be enclosed by single quotes. The first field value is returned for n=1. If n is larger than the number of field values, NULL is returned.
FieldIndex( fieldname , value ) Returns the position of the field value value found in the field fieldname (by load order). If value cannot be found among the field values, 0 is returned. fieldname must be given as a string value, e.g. the field name must be enclosed by single quotes."
Thanks for the response. I understand from help that the quotes would be required. But my understanding was that this will be true if literal is passed. In this case, you are passing a FieldName. I tried your sample and removed the quotes around the OverRideID in the fieldindex function. It still works.
Sorry but just trying to understand the QV syntax here.
Ah, sorry for my confusion. If your field name doesn't have any white space or funny characters, you can get away with not putting it in double quotes. If it DOES have white space or funny characters, you must put the field name in either double quotes or in brackets . I don't always follow my own standards when posting examples, but in my real applications, I try to always put field names in double quotes for consistency, whether they have white space in them or not.