Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

Dear all,

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

 

Table 1:

IDAmount per ID
1217
2174
3139
454
530
6254
758
8340
91
1087
1199
12141
13158
1418

 

Table 2:

Amount per PlacePlace
126623
504441

 

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
cplewis90
13 - Pulsar
13 - Pulsar

Hey @Alexander_Gruber_FhA,

 

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.

cplewis90_0-1607010695573.png

cplewis90_1-1607010716999.png

 

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.

cplewis90_2-1607010759137.png

cplewis90_3-1607010793085.png

 

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.

cplewis90_4-1607010838552.png

cplewis90_5-1607010855524.png

 

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

DaisukeTsuchiya
13 - Pulsar

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

1.JPG

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

2.JPG

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

3.JPG

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

4.JPG

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

5.JPG

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

Tokimatsu
12 - Quasar

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

2020-12-04_15h04_24.png

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

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

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

Tokimatsu
12 - Quasar

@Alexander_Gruber_FhA 

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