topic Re: Dynamically calculate points for a metric based on formula stored in metadata. in Alteryx Designer Desktop Discussions

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

rparimi — Mon, 06 Sep 2021 23:33:11 GMT

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

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

apathetichell — Tue, 07 Sep 2021 00:51:06 GMT

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

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

rparimi — Tue, 07 Sep 2021 04:20:37 GMT

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.

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

rparimi — Tue, 07 Sep 2021 04:23:18 GMT

This is how far I came with the sample data.

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

OllieClarke — Tue, 07 Sep 2021 10:31:46 GMT

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

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

atcodedog05 — Tue, 07 Sep 2021 11:05:31 GMT

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 : )

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

OllieClarke — Tue, 07 Sep 2021 11:09:32 GMT

Ah @atcodedog05, a complete solution! Nice 🙂

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

rparimi — Tue, 07 Sep 2021 15:22:38 GMT

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.

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

atcodedog05 — Tue, 07 Sep 2021 15:28:42 GMT

Happy to help : ) @rparimi

Cheers and have a nice day!