Join

[Analyst_12345]

Hello I have 2 data sets: one contains all the fields and another one contains values for particular fields that i dont want in analysis at all. Kind of pre filter before analysis.

For example if company code is 0100 then i dont want this in my data.

so i used a join tool, and matched on the basis of company code and if there is a match i would exclude it. And i was able to get desired results.

but now in pre filter I have got one more field and value to exclude. Example: if tax code is 1A, then remove it also.

so now the pre filter file would be using 2 fields company code and tax code.

now if i use join tool on the basis of these 2 field names, it will use an AND condition by default. Is there any way to exclude these fields using OR condition instead of AND condition. 

attached the pre filter data. Which i will match with my main data set. Please advice a way which can be used to exclude particular values from these fields separately and dynamically as the fields in this data set might increase in future

[taylor_butler]

Hi @Analyst_12345 , 

As always with Alteryx, there are probably a number of different ways to achieve this, so I've attached my solution for reference. Ultimately, I use the same approach you described in your post, but before joining the data, I transposed my raw data so all the values are in one column and all the field names are in the other - this allows you to just join based on the values that are within your pre-filter criteria file (as opposed to joining on multiple different fields). 

Hope this helps - please let me know if you have any questions!

Taylor

[Analyst_12345]

Hello,

thanks for your response. But a small doubt. If i transpose the raw data, and match on the basis of values I would be able to remove only those values. What about the complete row ? I want to remove the complete row that matches the pre filter condition but because of transpose i wont be able to remove the other values in the same row

[taylor_butler]

Hi @Analyst_12345 , The second join identifies the entire row that needs to be removed, based on if it's Record ID matched any of the pre-filters.