Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MrBosch
Creator
Creator

Calculate a score based on a matrix with dimensions and conditions

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.

 

QV Excel Matrix.png

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

Labels (1)
3 Replies
Mark_Little
Luminary
Luminary

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

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

EdgarOlmos_0-1663257723253.png

 

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

Regards

MrBosch
Creator
Creator
Author

Thank you so much. That will do the trick!