Join two files using multiple Equal To and Not Equal To conditions

[sarthak2312]

Hello Everyone,

After going through bunch of different solutions on community I decided to post here as I didn't find the right solution for my task.

I have one file into which I need to assign 'City' values from below table by matching its values from the below table. The values which starts with '<>' indicates not equal to. If all the values of a row matches from the below table then respective 'City' values should be assigned.

City	Product Code	Zip Code	Distribution Code	Production Facility Code
New York		85420	Y75125	IBT 525
Dallas	58645	85421	Y75126	BCT7653
San Antonio	58646	<> 85422	<> Y75127	HJP526 HYTG
Austin	58647	<> 85423	Y75128	<> HFG2653 YTF
Los Angeles	58648	<> 85424	<> Y75129	<> DGE878 TYRD
San Fransisco	58649	85425	Y75130	JGF15161
Sacramento	58650	85426	<> Y75131	IRKDB5987 JFH
Seattle	58651	<> 85427	Y75132	DSJAF6546
Chicago	58652	85428	Y75133	<> SHVAJ546
Kansas City	58653	<> 85429	Y75134	SDAHA25454
Orlando	58654	85430	Y75135	<> FGDS2154 HFGH
Miami	<> 58655	85431	Y75136	DADFE215 4545
Memphis	58656	85432	Y75137	FDSG2154 SD
Oklahoma City	<> 58657	85433	Y75138	BFDGH214
Houston	58658	<> 85434	<> Y75139	GHJGA546
Denver	58659	85435	Y75140	FBDSA154

I am not sure how can i tackle it more efficiently other than creating different sets of rules and making complex workflow. I have attached the sample file with File 1 sheet contains data in which I need to add city based on conditions provided in File 2 sheet. Thanks in advance.

[ponraj · Accepted solution]

Sample workflow. 

[sarthak2312]

Thanks Ponraj for prompt response. I apologies for attaching wrong sample file. I have updated the sample file. Can you please help? Thanks!

[jarrod · Accepted solution]

Hi @sarthak2312 

i've attached a workflow that should work for you.

the main part of the strategy i used was to create "criteria" fields that i can use in a formula downstream. since we can't "negatively" join on fields in the same join tool, we instead need to join on one field that is always true (or append if you really have all fields that differ in logic). Then you can apply a formula (or sets of filters) to create the logic needed to append the city names.

here's the formula i created in a single filter tool:

//zip code test
(//zip code = right zip code AND the requirement to not have the zip code match is false
([Zip Code]=[Right_Zip Code] && ![NOT_Zip Code]) 
//or zips don't match and we want that ("Not_zip code" will be true)
|| ([Zip Code]!=[Right_Zip Code] && [NOT_Zip Code]))
&&
//Distribution Code test
(//Distribution code = right Distribution code AND the requirement to not have the Distribution code match is false
([Distribution Code]=[Right_Distribution Code] && ![NOT_Distribution Code]) 
// Distribution codes don't match and we want that ("Not_Distribution code" will be true)
|| ([Distribution Code]!=[Right_Distribution Code] && [NOT_Distribution Code]))
&&
//Production Facility Code test
(//Production Facility code = right Production Facility code AND the requirement to not have the Production Facility code match is false
([Production Facility Code]=[Right_Production Facility Code] && ![NOT_Production Facility Code]) 
// Production Facility codes don't match and we want that ("Not_Production Facility code" will be true)
|| ([Production Facility Code]!=[Right_Production Facility Code] && [NOT_Production Facility Code]))

 it's basically looking at two conditions to be met for each nested field (zips match AND they are supposed to OR zips don't match and they are supposed to not match)

alternatively, you can use filters:

see the workflow for more details.

[sarthak2312]

Hello Jarrod,

Thank you for the solution. Somehow I am not able to open the workflow. It would be great if you can re-share the file. I have also found a simple solution of it. I basically combined two files with many to many relationship and then compared the criteria and assigned mark whether the record matches the criteria. I have attached the workflow here for your reference and your review of the solution. Thanks.

[jarrod]

i changed the version number to hopefully fix the issue. let me know if you still can't get it to work.