Iterative loop

[maverick_70]

Hello,

I have a some what of a tricky workflow that I need help with.

I have a table which have the following information: It has 4 columns Part 1, Part2, Part1(m/b) and Part2(M/B).

Part1	Part2	Part1(M/B)	Part2(M/B)
ABC	a1234	M	B
ABC	b4321	M	B
CDE	a8989	M	M
CDE	a4344	M	B
CDE	a3434	M	B
EFG	a3131	M	M
EFG	a9999	M	M
a3131	a7979	M	B
HIJ	a6464	B	M
KLM	a8765	B	B
a8989	a6767	M	M
a6767	a8787	M	B
a9999	a5454	M	B
NMO	a8967	M	M

I am trying to build a table which would look like this.

Part	Result
ABC	a1234,b4321
CDE	a8787,a4344,a3434
EFG	a7979,a5454
a3131	a7979
HIJ	HIJ
KLM	KLM
a8989	a8787
a6767	a8787
a9999	a5454
NMO	a8967

The conditions I am using are

if Part1(M/B) = M and Part2(M/B) = B then Part2

if part1(M/B) = M and Part2(M/B) = M then look the value of part2 in the Part 1 column and again check for the conditions if part1(M/B) = M and part2(M/B) = M then check the value in part2 column in column 1 and check for the conditions again if part1(M/B)= M and part2(M/B) = B then part2

I am going down each level until Part2 M/B = B or if there is no part 2 in part1 column. The purpose is to go down at the deepest possible level.

The table with the result column is what I am looking for.

Your help would be greatly appreciated.

[AbhilashR]

Nice one @grossal! 

[grossal]

I have adapted it @maverick_70. Give it a try.

We might stumble across another issue, but I am ready to fix everything that occurs. It's hard to prepare a workflow for all possible things when the sample data is limited. I hope it works without another fix, but we have to find out 😃

Let me know if it worked.

Best

Alex

[grossal]

Thanks!

I already had you in mind when typing "I am sorry, I think I have to use Python here, but XXX and XXX mind find an Alteryx-way".

These problems are kinda tricky in Alteryx (at least for me). It's much easier in Python or in this case, in Haskell. I am glad I found an Alteryx way, but I am still not really happy the way we have to do these things.

How would you do this?

[maverick_70]

Hi Alex,

Found some issues:

Part1	Part2	Part1(M/B)	Part2(M/B)	New Column
ABC	a1234	M	B	a1234
ABC	b4321	M	B	b4321
ABC	example	M	M	example(should be 'abc')
CDE	a3434	M	B	a3434
CDE	a4344	M	B	a4344
CDE	a8989	M	M	a8989(should be a8787)
EFG	a3131	M	M	a3131(should be a7979)
EFG	a9999	M	M	a9999(should be a5454)
HIJ	a6464	B	M	HIJ
KLM	a8765	B	B	KLM
NMO	a8967	M	M	a8967
a3131	a7979	M	B	a7979
a6767	a8787	M	B	a8787
a8989	a6767	M	M	a6767(should be a8787)
a9999	a5454	M	B	a5454
example	abc	M	B	abc

i have added some comments in the new column.

[grossal]

The logic was already working right, but I messed it up to clean the output right, therefore the Join in the main workflow made mismatches and the results looked wrong. Sorry that I missed that.

I have added a part in the end to make it easier to check if it works on a bigger sample.

An updated version is attached.

Best

Alex

[maverick_70]

Brilliant! I will test it with my dateset and will reach out if anything comes up!

Thanks again Alex!

[Ben_H]

Hi @maverick_70

I know this is already solved but I needed some practice with Iteration!

My solution is probably fairly similar to the existing one but here it is anyway.

I contained the logic within the Macro - The macro works out the correct output (I think) and only outputs recordID and the output value, I then just join onto the original list to get the original values + output.

The iteration list reads in and applies the logic you specified, anything with a simple output gets sent straight to the output stream. Records that require further work are then joined to the second input which is just the full data set.

Any that join - I keep the left record ID and the rest of the data from the right input and send to iterate again.

Those that do not join (left) Just take the part2 value and get sent to output.

I *think* this works.