Join with wildcard for some fields

I would like to join 2 tables together and I don't know a good way to do it. Table A has all the real possible combinations of categories that I need, which is about 50,000 possibilities. Any "Total" in the data in Table A (highlighted in yellow) means any value will match.

Table B has actual demand and forecast data at the lower product # level with about 25,000 records that I will later use to run some forecast accuracy calculations.

I want to join the two tables together with the grey headed columns as key fields (right join) but using a sort of wildcard if there is "Total" in one of the fields of Table A.

For example, Records 1 and 2 from Table B should join with Category row 2 of Table A because any region matches "Total". However, only record 1 from Table B should join with Category row 1 of Table A because that category row needs region AIM specifically. Obviously, the table will expand into millions of records when this join is done.

I don't know if this can be done with some iterative macro or some other trick. Can someone help?

Join

Iterative Macro

Accepted answers

joshuaburkhow

Hey @TLyle

This should do it. MIght not be the best approach once the data gets real big but I think this gives you the idea. You could turn this into a batch macro to do this join row by row so you don't have to do the append. Wanted to give you something that you could start with. Let me know if this works for you!

Joshua

Join - Multiple Parameters.yxzp

All comments

Hannah_Lissaman

Hi there!

I would love to help with this situation - it sounds like a really interesting challenge. I have a couple of questions about the data before I can think about the best way forward:

1) First of all, what are you planning to use this data once the join is completed? Will the totals (with their multiple rows) be used to sum the values in table B and get back to one row for your original table A, or do you plan to have a final data set with multiple rows for each of those totals? Once the data is created, will you be using this for reporting, and in what format/software?

2) Is your table A meant to contain all possible combinations of totals, or will you only need a subset of the possible roll up totals? I ask because I would anticipate the total number of possible combinations is very large (probably larger than 50,000 - there are 52 combinations of different totals/non totals for a single row alone). It is important to understand if we are dealing with very large data volumes if we are going to consider using an iterative approach.

joshuaburkhow

Hey @TLyle

Joshua

Join - Multiple Parameters.yxzp

joshuaburkhow

Of course I couldn't help myself and went ahead and created the batch version that I think should perform better as it limits the amount of appends that need to happen by batching by RecordID. Spot check to make sure this gives you what you need 🙂

Batch Join - Multiple Parameters.yxmd

geraldo

Hi,

Take a look at this junction as the data went from how you want to final table was not easy to understand.

Too much information.

Here's a workflow to see if it helps.

[]

JOINW.yxmd

benakesh

@joshuaburkhow Excellent solution . This could be next weekly challenge !. I think macro is not attached .

TLyle

Thanks Joshua, for sure that was the simple logic that I thought might exist but I couldn't figure out. This solution works but i think your macro solution will be more scalable as the records will expand to a huge number before i par them down a couple steps later.

TLyle

@joshuaburkhowcould you post the macro as well? I can't load the workflow without the macro.

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167