Dynamic Filter or Join based on Different Columns and Values

[tj]

I have 2 tables to work with.

First table contains product and sales information separated by region/country as shown below

Product	Region	Country	Sales
A	North America	United States	1000
A	South America	Brazil	1500
A	Asia	Japan	2000
B	North America	United States	350
B	Europe	Germany	500
B	Asia	Singapore	360
B	Africa	South Africa	800
C	North America	Canada	450
C	Europe	France	950
C	Africa	Kenya	200

Another table is condition table based on region/country as follows

Level	Details
Global
Regional	Asia, Africa
Country	United States, Germany

What I want to achieve is either use filter (preferred) or join to dynamically subset product table based on condition table specific row. 

• Global - do NOT filter anything
• Regional - filter all Asia and Africa product sales

Product	Region	Country	Sales
A	Asia	Japan	2000
B	Asia	Singapore	360
B	Africa	South Africa	800
C	Africa	Kenya	200

• Country - filter all US and Germany product sales

Product	Region	Country	Sales
A	North America	United States	1000
B	North America	United States	350
B	Europe	Germany	500

Ideally I'd like to use filter tool with condition can be updated dynamically, alternative would be join (but not sure how to do with global level). 

I researched various community posts, including macros, control parameters, dynamic filter/join, etc., but still not able to wrap my head around for an elegant solution.

Much appreciate your thoughts and ideas!

[Greg_Murray · Accepted solution]

@tj 

Take a look at this. I am not sure exactly how you expect the output, but I think this is a start. 

[tj]

Hi @Greg_Murray 

Thanks a lot for your quick help, the output seems to work as intended. Append and filter tools did the trick!

One thing I'm not certain is how well it would perform for large dataset (i.e. million rows) which would append each condition to every row.

I'll test out your solution to see how it works, and would be curious to see if there's other solution available to dynamically update filter condition without appending/join the tables.

Thanks again for your insights!

[Greg_Murray]

Hey @tj , 

Alternatively you could take an iterative approach to filtering each set of conditions with a batch macro. See attached. This one is a little trickier. It creates the expression for each condition and then dynamically updates the XML in filter tool to in the batch macro. This should be more performant at scale.

[tj]

Excellent, thanks @Greg_Murray 

Will try out this week and report back which one works better.

Thanks!

[tj]

Hi @Greg_Murray,

I tested solution #1 and it's working as intended, I went ahead to mark your #1 solution.

A couple of questions on the solution:

1. If I have multiple condition, i.e. one is global/region/country, another one is department/function, etc. Am I able to construct filter statement to have OR condition within while AND condition across?
2. Is there a way to pass file value (Asia, Africa) to value IN statement, instead of split into row and append by each? I'm struggling to make it work as IN doesn't seem to take the value correctly (even after adding " or ")

Much appreciate your response!

[Greg_Murray]

Hi @tj,

Based on what you're saying I think the second solution is more in line with what you want. You should be able to create expressions with multiple conditions to meet whatever requirement you want. 

Greg