Merging 2 datasets on reference and value (with a tricky touch)

[SébastienG]

Hi guys,

I got a challenge that I cannot solve (don't even know if Alteryx can do it, but I trust in the community's power):

We have 2 tables, which we want to merge based on the 'reference' and on the (sum of) 'value'.

The values we want to keep in the final table, are those that constitute the larger value (omitting this larger value).
Here follows an example to make it clearer:

Table1:

Type	Reference	Value
X	Ref1	4
Y	Ref2	5
Z1	Ref3	1
Z2	Ref3	2

Table2:

Type	Reference	Value
A1	Ref1	1
A2	Ref1	3
B	Ref2	5
C	Ref3	3

Target table:

Type1	Type2	Reference	Value
X	A1	Ref1	1
X	A2	Ref1	3
Y	B	Ref2	5
Z1	C	Ref3	1
Z2	C	Ref3	2

You guys would help me a lot when solving this! I'd like to turn some data in a nice sankey graph.

[Jonathan-Sherman · Accepted solution]

Hi @SébastienG,

I think this solves what you're trying to achieve? Join the two tables together and use a formula to decide which value to keep?

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[SébastienG]

I couldn't get my head around how it would work with the values, but you completely nailed it. Thanks!