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]

Thanks all for your replies.

I was able to resolve this using a reply suggested by Ajay143 on another thread. There a mapping load table was recomended as the solution;

MAP1:
Mapping Load SystemID,
Score
FROM dbo.....

RESULT1:
LOAD SystemID,
OverrideID,

Score,

If(ISNULL(SystemID)=-1,Score,ApplyMap('MAP1',OverrideID,Score)) as InheritedScore

FROM dbo....

[#3]

Hi John,

Just noticed that the fieldvalue function works a bit strangely (or the help file is not very clear about this). The n represents only distinct values and not actual values. So if you have field values which are repeated, one will land up in a problem. I just tried this even with example by giving same values to scrores in more than one record.

Have you noticed this?

Nimish

[johnw]

I've actually never used them in real applications, so no, I hadn't noticed. But yeah, I'm seeing the same behavior as you, which breaks that solution when values are duplicated. At least we still have the script solution, and there may be chart solutions as well.