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

Iterative loop

maverick_70
7 - Meteor

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).

 

Part1Part2Part1(M/B)Part2(M/B)
ABCa1234MB
ABCb4321MB
CDEa8989MM
CDEa4344MB
CDEa3434MB
EFGa3131MM
EFGa9999MM
a3131a7979MB
HIJa6464BM
KLMa8765BB
a8989a6767MM
a6767a8787MB
a9999a5454MB
NMOa8967MM

 

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

PartResult
ABCa1234,b4321
CDEa8787,a4344,a3434
EFGa7979,a5454
a3131a7979
HIJHIJ
KLMKLM
a8989a8787
a6767a8787
a9999a5454
NMOa8967

 

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.

16 REPLIES 16
grossal
15 - Aurora
15 - Aurora

Edit: Ignore below. I noticed my mistake. The first rows of the data looked to obvious and I didn't take a deeper look into it. This will most likely require an Iterative Macro. If nobody else got it until I am back, I'll give it a shot.

 

----

Hi @maverick_70,

 

I might miss something here, but your example could be summed up with a simple logic:

 

grossal_0-1588181338549.png

 

Output:

grossal_1-1588181353252.png

 

From what I see, you always concatenate the Part2s if the have the same Part1. This can be accomplished using the Summarize Tool.

 

 If this doesn't work out, let me know and I'll build a Multi-Row-Formula for you! Workflow attached for reference. Let me know what you think.

 

Best

Alex

maverick_70
7 - Meteor

I was thinking the same thing but kind of stuck on where to start.

grossal
15 - Aurora
15 - Aurora

Don't worry, it's on my project list for tonight 😃

 

We'll find a way to get this solved!

grossal
15 - Aurora
15 - Aurora

Hi @maverick_70,

 

I have been trying to figure out the ruling, but I think I am missing something or your sample is wrong.

 

When I check the first row - Part1(M/B) is M and Part2(M/B) is B -> your rule applies -> it's Part2

Same with the second row. 

 

Your rules don't tell me to concatenate at any point, so the Result would just leave the rows as they are.

 

The rules for the lookup applies on the third row. Therefore I'd lookup a8989, find a6767 and a8787, but they aren't applied at all in the CDE row of your results. What's going on here? But part1(M/B) and part2(M/B) are both M -> so we should go deeper. But what happens after going deeper? Why doesn't the result show up something else than just the concatenation of all CDE values?

 

Can you double check your ruling and your sample data/result?

 

Best

Alex

 

 

maverick_70
7 - Meteor

This is what I am looking for

Part1Part2Part1(M/B)Part2(M/B) 
ABCa1234MBa1234
ABCb4321MBb4321
CDEa8989MMa8787
CDEa4344MBa4344
CDEa3434MBa3434
EFGa3131MMa7979
EFGa9999MMa5454
a3131a7979MBa7979
HIJa6464BMHIJ
KLMa8765BBKLM
a8989a6767MMa8787
a6767a8787MBa8787
a9999a5454MBa5454
NMOa8967MMa8967

 

 Once the data is in the above format, I could use the summarize tool to group and concatenate.

 

There are 4 conditions
if part1(M/b) = 'M' and part2(m/B) = 'B' then part2
if part1(M/b)= 'B' and part2(M/B) = 'M' then part 1
if part1(M/b)= 'B' and part2(M/B) = 'B' then part 1
if part1(M/b) = 'M' and part2(m/B) = 'M' then Go down one level and look part2 in part 1's column. This will create a loop which will go down at the deepest possible level and will stop when either of the first three conditions are met.

There is another condition which says:
if part1(M/b) = 'M' and part2(m/B) = 'M' then Go down one level and look part2 in part 1's column. If the part 2 cant be found in the part 1 column, then just give me part 2.

 

 

grossal
15 - Aurora
15 - Aurora

This was quiet a hard challenge, but surprisingly I found a way without going to Python (I was already typing a message "I'll use Python" in your answer window).

 

It won't win any beauty award and uses some trickery that isn't very clean, but it does the job.

 

Macro:

grossal_0-1588274423258.png

 

 

Main Workflow:

grossal_1-1588274433794.png

 

What happens in the macro:

- I join all rows (that haven't been resolved) with the lookup (just a copy of the original data)

- I do the lookup calculation and filter all rows to the top that got solved

- All rows that a still unsolved to go the bottom

- All formula tools in the macro help to do the trick. I have to concatenate and resolve the strings for the join to keep the original information in the data set

- The selects help to remove the joined columns

 

What happens in the main workflow:

- I solve everything possible but give it to the macro anyway (it'll filter out solved data)

- I use the filter to process the already sorted data

- I combine the already solved data with the macro solved data

- I use the Join tool on the original data to bring back columns that the macro missed because it couldn't find a lookup

- I union all together

- Formula to fix the rows that the macro couldn't solve because they had no lookup

 

I make one assumptions at the moment and that's "Part" has a length of 5 (all sample data did), but I already prepared it to be reworked when the length changes. We would only need to replace to formula tools with a Regex Tool if we want more flexibility with the length of the Part2 column. I already had some kind of headache and went with the easy way first. I'll grab a drink and be ready to adapt it for you in the next step, I just need a break for now 😃

 

 

Workflow and macro are attached. Let me know what you think.

 

Best

Alex

maverick_70
7 - Meteor

This is great! It's giving me exactly what I need,.

Thanks Alex!!

grossal
15 - Aurora
15 - Aurora

That's great to hear!

 

I still don't like the Iterative Macros, but exercises like this one help me to get there 😃

maverick_70
7 - Meteor

I think I might need to use regex. String length is changing.

Labels