Conditional Bifurcation

[knnwndlm]

Hi SME,

I have a list of County, City, State, and Zip Code.  I need to bifurcate the list into two categories - one based on one zip code mapping to one city (or same city with multiple zip codes) and the other on one zip code mapping to multiple cities.  

For example, in the first category, I would have the following:

OTM	One city to many zip codes
95916	Berry Creek
94511	Bethel Island
90209	Beverly Hills
90210	Beverly Hills
90211	Beverly Hills
90212	Beverly Hills
90213	Beverly Hills
96009	Bieber
96010	Big Bar

In the second category, I would have the following:

MTM	One zip code to many cities
91748	Rowland Heights
	City of Industry
	La Puente
96089	Summit City
	Shasta Lake
91390	Santa Clarita
	Agua Dulce
	Canyon Country
	Green Valley

I attempted to do this in a number of ways but always ended up finding exceptions.  Could somebody please help me figure out a better way to do this so that it's clean?  Appreciate your help.

Thanks,

kwl

[PanPP]

Hi @knnwndlm 

Can you provide a sample output for both inputs?

[knnwndlm]

Hi @PanPP ,

You can create a column showing OTM as the output for the first category and MTM as the output for the second category.

Thanks,

kwl

[Felipe_Ribeir0]

Hi @knnwndlm 

Please see if the attached workflow is doing what you need

[knnwndlm]

Hi @Felipe_Ribeir0,

The total number of records coming in is 4297 - the same number in the top branch.  The top and bottom branches should add up to the same number of records.  I think the bottom branch is working - the top branch seems to pick up more records.  Think I just need to remove anything that shows up in the bottom branch from the top branch - not sure about the best way to do this.

Thanks,

kwl

[Felipe_Ribeir0 · Accepted solution]

Hi @knnwndlm 

Try this new one

[knnwndlm]

Thank you @Felipe_Ribeir0!

[knnwndlm]

Hi @Felipe_Ribeir0,

I just noticed that CABAZON appears in both lists because CABAZON has two separate zip codes.  However, I don't think it should have been the case.  Not sure why.  Any suggestions?

Thanks,

kwl

[Felipe_Ribeir0]

Hi @knnwndlm 

This is happening because you have this ZIP_CODE associated with 2 different cities

[knnwndlm]

Hi @Felipe_Ribeir0,

Is there a way that we can change it so that CABAZON only shows up in the MTM branch and not the OTM branch?  Not sure how to approach this.  I've attached a revised workflow to combine the two branches into one.

Thanks,

kwl