Dynamically calculate points for a metric based on formula stored in metadata.

[rparimi]

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

[apathetichell]

go to help - one tool workflow examples - dynamic replace.

[rparimi]

Thanks for the suggestion. I've tried that. Dynamic Replace is not helping because there is "n" rows of of formulas, it will add n columns as output. In my case, I just need one column for output that matches the formula associated with metric. Do you have an sample you can share as I'm running out of Ideas. 

[rparimi]

This is how far I came with the sample data.

[OllieClarke · Accepted solution]

Hey @rparimi 

Dynamic replace is still the way to go, you just need to do a bit of transforming to get the output you want 🙂

Hope that helps,

Ollie

[atcodedog05 · Accepted solution]

Hi @rparimi 

Interesting use case. Coincidentally I am also working on a similar project. My workflow is an extension of @OllieClarke (Thank you @OllieClarke from your workflow I was able to get few ideas to refine steps)

Workflow:

If my solution helps please mark @OllieClarke 's post also as a solution since mine is just an extension of his 😅

Hope this helps : )

[OllieClarke]

Ah @atcodedog05, a complete solution! Nice 🙂

[rparimi]

Thanks @OllieClarke @atcodedog05 @apathetichell for the solution and help on my problem statement. This is exactly what I need and  solution will work for me. I will need to make additional changes, have additional columns/formulas to work with. 

I will keep you posted if I am stuck further in my solution but thanks very much for all the help. 

[atcodedog05]

Happy to help : ) @rparimi 

Cheers and have a nice day!