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.
Product | Region | Country | Sales |
A | Asia | Japan | 2000 |
B | Asia | Singapore | 360 |
B | Africa | South Africa | 800 |
C | Africa | Kenya | 200 |
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!
Solved! Go to Solution.
Take a look at this. I am not sure exactly how you expect the output, but I think this is a start.
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!
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.
Excellent, thanks @Greg_Murray
Will try out this week and report back which one works better.
Thanks!
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:
Much appreciate your response!
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