Simplifying ratios, highest common factor

[PaulRB]

I have a dataset where each row contains two integer values which represent a ratio. For each row, I want to "simplify" the ratio between these two numbers.

For example, if the two numbers are 20 and 10, the ratio 20:10 can be simplified to 2:1

Other examples:

2:2, 3:3, 27:27 are all simplified to 1:1

14:18 is simplified to 7:9

The way I am approaching this is to find the Highest Common Factor between the two numbers. Then I can divide both numbers by this HCF.

Using that last example again, the HCF of 14 & 18 is 2. Dividing both numbers by 2 gives me the desired values 7 & 9.

To find the HCF, I am attempting to implement Euclid's Algorithm. lets call the two numbers to be factored "A" and "B"

Step 1: sort them so that A is the higher value

Step 2: C = Mod(A, B)

Step 3. If C is zero, then we have found the HCF and its value is B. End of algorithm

Step 4. If C is not zero, then replace A with B and B with C and go back to step 2

Using the same example of 14 & 18:

Iteration 1:

A = 18

B = 14

C = Mod(18, 14) = 4

Iteration 2:

A = 14

B = 4

C = Mod(14, 4) = 2

Iteration 3:

A = 4

B = 2

C = Mod(4, 2) = 0

No more iterations required, HCF of 14 & 18 was the last value of B which was 2.

Here is my attempted Alteryx flow. Unfortunately, as you can see from the error message, the way I am trying to do this is obviously an abuse if the Union tool!

Can the forum members please suggest a simple approach to this problem?

[jdunkerley79]

You would need to do this as an iterative macro, removing rows which have been completed and looping the others round.

Very quick go attached

[PaulRB]

Thanks very much @jdunkerley. Unfortunately I can't open that. Any chance you can save it in format suitable for 10.0.9? I am in a corporate environment and don't have the option of upgrading any time I like...

[jdunkerley79]

Try these

[PaulRB]

No... your first attachment opens with an error about "missing or inaccessible macros" and appears to be corrupt to Alteryx 10.0.9. Your second attachment won't open at all, with the same "earlier version" error I got before.

I've been looking at the iterative macro example I have in Alteryx 10.0.9, the "supply & demand" one. I'm baffled as to how or even if this implements any kind of feedback loop where data requiring another iteration is fed back to the macro's input data.

[jdunkerley79]

One last try then.

Download to same folder and I hope should work (only have 10.5 so attempting to force the file back to 10.0).

[PaulRB]

Better but still problems. The macro workflow seems to open OK. The other workflow still gives that "missing macro" error, seems to have missing data flow lines and refuses to run.

I see that the macro workflow has two outputs, L and R, also called "Loop" and "Results". What tells Alteryx that the "Loop" output data needs to go around for another iteration, and the "Results" data does not?

Thanks again for you help on this. I must be close to understanding now!

[jdunkerley79 · Accepted solution]

The Workflow is set to be an Iterative Macro. This is specified in Workflow - Configuration.

The loop is specified in the Interface Designer