Calculate a score based on a matrix with dimensions and conditions

[MrBosch]

Hi all

I have a challenge and a long list of different kind of dimensions (age, sex, ...). Depending their values a score should be looked-up and added to a final score. 

I have looked at the existing matrix in Excel and created a database containing the 'conditions' for all dimensions.

So the first is easy: if the subject is a male add 1 point to the score (if it is a female add 5).
Now the 'young male value' (which is read from another database) needs to be evaluated against the conditions in the series ref-val-ymv-1 to 9. So let it be 5,5 for now: it will not match against the condition ref-val-ymv-0 [<3,5] (add 0) but  the condition in ref-val-ymv-4 and therefore add 4 to the total score.

So...
SUM(
If sex=male -> score = 1 
If sex=female -> score = 5
If youngmalevalue = 5,5 -> score = 4
... )
To get to the final score.

The example above is an abbreviated list. The number of dimensions and their 'lookup'-conditions is rather limited but the original will contain about 70 rows and 20 columns therefore making it painstaking to evaluate all dimensions one by one creating formulas and even then adding it up to some final score.

Hope someone can help me.

Alex

[Mark_Little]

Hi @MrBosch 

I would personally look at the scripting options for this, you have the data. First thoughts from me, load left join the scores to your fact table. Then resident load, the key and scores for the fact table and drops the scores from the fact table. We now have your fact table and table. Next crosstab the scores table you three fields Key, Score type and score. 

Then front end you can just SUM(Score), or limit using set expressions {<[Score Type]={'Age'}>}

Hope this makes sense and helps

[EdgarOlmos]

Hi.
An option it's to solve the problem using the script editor.
You can make a mapping table like this:

After that, in the load of the transactional data with an applymap you can find the value that you need.

Regards

[MrBosch]

Thank you so much. That will do the trick!