I have a requirement where
a) Metrics are stored in excel as metadata with a formula. User can add new metrics with formulas.
| Metric | Formula |
| Critical infrastructure designation | IF [Raw Score] = 'No'
THEN 0
ELSE 5
ENDIF |
| % of high risk sites segmented and monitored | IF [Raw Score]='100%'
THEN 0
ELSEIF [Raw Score] >='85%' && [Raw Score]<='99%'
THEN '1'
ELSEIF [Raw Score]>='71%' && [Raw Score]<='84%'
THEN 3
ELSEIF [Raw Score]<='70%'
THEN 5
ELSE 0
ENDIF |
| # of non-segmented sites | IF [Raw Score]=0
THEN 0
ELSEIF [Raw Score] >=1 && [Raw Score]<=5
THEN '1'
ELSEIF [Raw Score]>=6 && [Raw Score]<= 10
THEN 3
ELSE 5
ENDIF |
b) Also there is sheet where User has raw_score for Input Metric data. For ex:
ID Metric Raw_Score
| 1 | Critical infrastructure designation | Yes |
| 2 | # of non-segmented sites | 4 |
| 3 | Critical infrastructure designation | No |
| 4 | Critical infrastructure designation | No |
| 5 | % of high risk sites segmented and monitored | 80 |
| 6 | # of non-segmented sites | 21 |
| 7 | % of high risk sites segmented and monitored | 40 |
3) I am expecting the Alteryx workflow to dynamically calculate the points based on raw_score by applying the formula for the metric which is defined in '1'
ID Metric Raw_Score Points
| 1 | Critical infrastructure designation | Yes | 5 |
| 2 | # of non-segmented sites | 4 | 1 |
| 3 | Critical infrastructure designation | No | 0 |
| 4 | Critical infrastructure designation | No | 0 |
| 5 | % of high risk sites segmented and monitored | 80 | 3 |
| 6 | # of non-segmented sites | 21 | 5 |
| 7 | % of high risk sites segmented and monitored | 40 | 5 |
Can you please recommend what tool can accomplish this type of output?
Thank you,
--Ramesh
