Filter Values in a Group

[Sarath27]

Hi All,

I badly need your help on this where I am stuck for days. I have 287291 records of data where I grouped by them "FULL NAME" giving Cluster ID. Now my next goal is to find the following

1. If any record in a cluster has "5 CANADA SQUARE" in its address line, then other records have same "Address" - Then "It should be labelled as "1"

2.If any record in a cluster has "5 CANADA SQUARE" in its address line, then other records have 2 or more different "Address" - Then "It should be labelled as "2".

3.If Cluster doesn't have ""5 CANADA SQUARE" in its address line", then it should be labelled as "3".

I know this is bit complicated, please help me out with this that'd be very much helpful for me.

[Sarath27]

As you can see below this Cluster_ID = 105, has "5 CANADA SQUARE" in ADDR_LINE1, but other address in the same cluster has two different "Address, so in this case it should be labelled as "2".

[Luke_C · Accepted solution]

Hi @Sarath27 

A bit tough with only screenshots of the data, but here's what I'd suggest you do:

1. Filter on ADDR_LINE1 = 5 CANADA SQUARE
2. Summarize the cluster IDs from the T anchor (this will be your clusters with 5 canada as an address)
3. Join those IDs back to your main data set. Anything that falls out will be your '3's 
4. From the J tool, do another summarize and count the distinct addr_line1 values. Anything with 1 distinct address will be your clusters with only 5 canada square, anything with more than 1 will have multiple addresses.

Give that a go and see how you make out. If you have any issues, some sample data and the workflow you tried will be helpful.

[Sarath27]

Hi Luke,

Thanks much for your prompt response. I tried, but it didn't work. I have attached sample data for each scenarios(1,2,3), please kindly create a workflow from this and guide me the way to achieve my desired output.

[Luke_C]

Hi @Sarath27 

Please share the workflow you attempted to create and I'll be happy to look and see where you might have gone astray.

[Sarath27]

Luke,

Please find the workflow, do the needful. Awaiting for your positive response in it.  Label 3 I have completed! Only Label 1 and 2 yet to be figured out

[Luke_C]

Hi @Sarath27 

I don't have access to your input.. please export the workflow as a yxzp so the data is included. Options>Export Workflow

[Sarath27]

Luke, Here you go!

[Sarath27]

Luke, please do let me know if you able to access it!

[Luke_C · Accepted solution]

Hi @Sarath27 

Try this. This is what I described above.