Join with wildcard for some fields

[TLyle]

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?

[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 · Accepted solution]

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

[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 🙂 

[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.

[]

[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.

[joshuaburkhow]

hopefully this attaches 

[joshuaburkhow]

Also I know a lot of us here would love to know how it actually performs on the large dataset. If you remember/can post how the macro did against the larger data I would love to know! 

thanks!

JB