Overide Value

[#1]

All,

A slightly unusual requirement.

We have some data as follows;

SystemID, OverRideID, SCORE

1, , 100

2, , 90

3, , 95

4, 1, 20

We would like to create a bar chart that shows SystemID and Score.

SystemID 1 should have a score value of 100

SystemID 2 should have a score value of 90

SystemID 3 should have a score value of 95

SystemID 4 should have a score value of 100 (Because we want it to inherit the score from SystemID 1)

We tried code such as the following but is doesn't work and the bar chart still displays the 20 value.

if(isNum(OverRideID), OverRideID, (if(isNull(OverRideID),SystemID))) as ID;

Any suggestions?

P.S. We don't want to override SystemID 4 in all cases. It will only be required for this chart.

Tom

[#2]

Correction

That last code example should read;

if(isNum(OverRideID), OverRideID, (if(isNull(OverRideID),SystemID))) as SystemID;

[#3]

Can you change your table structure to something like this:

SystemID, SCORE, OverrideScore

1, 100,

2, 90,

3, 95,

4, 20, 100

Then your expression would be something like this

=If(IsNull(OverrideScore), SCORE, OverrideScore)

[#4]

YOu could use the following expression

if(isNum(OverrideID),above(Sum(Score),SystemID-OverrideID),Sum(Score))

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.

Do let me know if this works for you.

Nimish

[johnw]

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.

if(OverRideID,fieldvalue('SCORE',fieldindex('SystemID',"OverRideID")),SCORE)

Both techniques are demonstrated on the example data in the attached file.

[#6]

Hi John,

This is really good. Is this expensive (especially over large data sets?). Also, are the double quotes required for the fieldvalue pararmeter in the fieldindex?

Nimish

[#7]

You can simply do it with the Hierarchy Load.

Check for it in the Qlik View Help.

[johnw]

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."

[#9]

Hi John,

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.

Nimish

[johnw]

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.