Conditional data manipulation using alteryx

[akshatkumar87]

Hey Guys,

I have a data set where i need to do manipulation based on various columns. I i need to get weighted average of Access_score over lives if sum lives!=0 and weighted average over TRx of Access_score if sum lives=0 and 'Trx' as unique of each value grouped by IMS_ID & IMS_PLAN_ID

Input data

IMS_ID	IMS_PLAN_ID	PLAN_ID	IMS STATE	PLAN STATE	ACCESS_SCORE	Lives	Trx
222	234	1234	CA	CA	2	0	3
222	234	678	CA	CA	4	0	3
222	235	1234	CA	CA	2	1000	4
222	235	678	CA	CA	4	200	4
333	234	1234	CA	CA	2	0	5
333	234	678	CA	CA	4	0	5
333	235	1234	CA	CA	2	1000	6
333	235	678	CA	CA	4	200	6

Output data

IMS_ID	IMS_PLAN_ID	ACCESS_SCORE
222	234	3
222	235	2.333
333	234	3
333	235	2.333

I am thinking of using mutifield formula but not sure how to manipulate multiple columns grouping by multiple columns

Thanks in advance

[RodL · Accepted solution]

I'm thinking you can just filter on your conditions and then use the Weighted Average tool (in the Transform category) for each side of the Filter tool and then Union them back together?

[michael_treadwell · Accepted solution]

Basically doing what @RodL stated. Check the attached workflow.

[jdunkerley79]

I tried using a summarise tool to get sum of lives, then filter to split in two taking it through the Weighted Average macro. Finally union back together.

Sample attached

(same as @RodL and @michael_treadwell I think!)

[akshatkumar87]

Thank you everyone for your help much appreciated.

[akshatkumar87]

Can you upload the version for 10.1 cannot open yours ?

[jdunkerley79 · Accepted solution]

@akshatkumar87 Try the attached 

[akshatkumar87]

Thanks a lot