Get Inspire insights from former attendees in our AMA discussion thread on Inspire Buzz. ACEs and other community members are on call all week to answer!
alteryx Community

Alteryx Designer Desktop Discussions

SOLVED

Join based on an accumulation of values to reach a specific value

7 - Meteor

Dear all,

I have a problem in my workflow regarding the "intelligent" join of two tables.

Table 1:

 ID Amount per ID 1 217 2 174 3 139 4 54 5 30 6 254 7 58 8 340 9 1 10 87 11 99 12 141 13 158 14 18

Table 2:

 Amount per Place Place 1266 23 504 441

I would like to join table 1 with the informations in column "Place" of table 2.

The total of "Amount per ID" in table 1 is equal to the total of "Amount per Place" in table 2. Alteryx should build the sum of the "Amount per ID" of specific ID´s to reach 1266. Then join these ID´s with Information "Place" (23).
The remainig ID´s, which should realise the sum of 504, should be joined with "Place" 441.

I´m aware that there are maybe more possible compositions to reach 1266. I just would like to have the first, fastest one.

I would appreciate it if someone could help me out. Thanks in advance,
Alex

7 REPLIES 7
13 - Pulsar

I put together a workflow for you but wanted to explain the steps I took.

I shifted the second dataset into one record instead of two by using some crosstab tools.

I then put that record onto the other dataset so I could create a smart running total that would reset when the 1st cutoff is hit.

From there I did some clean up on the data and found how to apply the other values (points) to the records below each cutoff.

Let me know if this is what you were looking for or want any additional explanation.

13 - Pulsar

I calculate all combination and find the combination match with Table2.

1)  Create all cases assigning 0 and 1 to each ID by row generation tool. (16,384 Cases)

2) Multiply Amount per ID by 0 and 1 in above table and sum value for each Case No.

3) Find the Case No match with 1266. (Total 13 Case)

4) Value 1 shows ID used for Place 23 and Value 0 shows ID used for Place 441 for each case.

This topic is interesting.
I would like to make this topic the basis of Weekly challenge. Can you allow that?

12 - Quasar

Here is my solution. I use math-bitwise-function for creating combinations. @Alexander_Gruber_FhA

7 - Meteor

Thank you @cplewis90 ,

your solution is the one I´m able to handle the most, especially when the logic has to be integrated in my real workflow.

Best regards and again thank you for the quick response,
Alex

7 - Meteor

Thanks @DaisukeTsuchiya !

Your solution seems to work too. But it becomes a little complex when it has to be integrated in my real workflow.

Br,

Alex

7 - Meteor

Thanks @Tokimatsu,

solution works, but generates a lot of additional rows. So the overall performance isn´t that good, beacause in my workflow there are a lot more "ID´s", which have to be joined.

Br,

Alex

12 - Quasar

If you need all combinations to much the table2,  my attached workflow is little bit faster than my old solution. It enabled AMP Engine.

And if you want to get only 1 combination that is much the table2, there is a possibility to apply optimization.

If so, please let me know the definition of best combination.

Labels