Multi Row Formula Row Aggregation

Hello, I am wondering how I can better aggregate my rows. I thought I had a clever idea by using the summarize tool and concatenating my reference numbers, but since every possible combination did not exist in the record set, I ended up getting this result. Is there a way turn the current record below to the desired record systematically. I thought that using the multi row formula could do this task, but I cannot make it work.

Current Record:		Desired Record:
Control#	Policy#	Control#	Policy#
A1234;Z4569	1ABCD	A1234;Z4569	1ABCD;9WXYZ
Z4569	1ABCD;9WXYZ

Workflow

Accepted answers

MinaGO3

How about this solution using Make Group?
Make Group.PNG

Make Group Workflow.yxzp

All comments

Thableaus

Hi @D_Sluss

Could you please provide a more complete input data and a desired output?

Still not so clear what you're trying to do here.

Cheers,

danilang

Hi @D_Sluss

Here's one way to get the output you're looking for

Start by transposing your data to get it all in one pair of columns. Split the values in rows using ";" as the delimiter and get the the unique name/value pairs. Cross tab to get the Control and Polciy back as column headers, concatenating the values and finally rename the the columns to get rid of the "_" that the Cross Tab tool puts in, resulting

This works for your sample data producing exactly the output you want, but as @Thableaus mentioned, we need more detail to create more generic solution. i.e. do you always want the output on one row, are there any other Key columns that need to be accounted for, etc

Dan

Aggregate.yxmd

D_Sluss

Sorry, for the vague initial post. I did not know exactly how to word. I completed a more thorough sample in the below database. I had to scramble the data quite a bit since it is sensitive data. I added a column called 'unique' which represents the level to which I am trying to condense the records (1 record by letter a-j). I added this 'unique' column manually so I do not have this available to group the data in the full database. As you can see the names are not reliable as a key even with fuzzy match.

So basically, the only way I have to consolidate the records is by comparing policy to get all med id's, look up the med id's to get all the polices, and then look up the policies again to get all the med ids. Eventually grouping into one record. However, I am not sure how to do this.

Again, sorry again for the inadequate explanation, but I am hoping my sample data will make things clear.

samples.yxdb

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703