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.

Multi-row multi-column checks and iterate related Question

Altusr1
7 - Meteor

Hello all,
First of all a big thanks to all the prior contributors as I have learnt a lot from this forum.
I do have a specific issue that brings about this question on how to best derive the workflow for the same.
Please open the attached .xls to follow along with the issue at hand.
First on the .xls sheet, you will see on the left, 5 columns named RecordID, Date, CAT1, CAT2, CAT3 respectively.  As you scroll down you will see some red fonts that are the undesired behaviors that we need to look for and on the right of the sheet you will see the same five columns (RecordID and Date are identical on both tables) but with the desired behavior in Green with an altered value called "UNDEFINED" based on the following logic to find these rows and columns.

Here are the steps that I want to achieve on the left 5 columns to accomplish the desired result. 

1. Start with Row 1 and check CAT1, CAT2, CAT3 for each row until you find the first ACT1 value on any of these 3 columns CAT1, CAT2 or CAT3.

2. For every row with ACT1, I expect that either of the other two CAT columns should have one of these category values like ACT2 or ACT3 or ACT4 or ACTNA1 or ACTNA2 or ACTNA3 or ACTNA4 or TAG but if both the other columns say "OTHER" then we have to crawl the other two category columns (one row at a time) until we find "ACT2" or "ACT3" or "ACT4" to determine which column entry to change.  For example, on the attached, ACT1 is found on RecordID=2 where the other two columns CAT2 and CAT3 read OTHER, then we keep looking to see which CAT column has the first instance of ACT2/ACT3/ACT4 and in this case, it was CAT2 on RecordID=12; once we find that, would like to change the values of all rows of that column from "OTHER" to "UNDEFINED" as shown on the right section of the attached .xls and not change anything else on other columns.

3. Another example is on RecordID=61, where we have ACT1 appears on CAT2, we see that TAG is on CAT1 column so we keep going down to find the next ACT1 on CAT1/CAT2/CAT3 columns

4. If we continue going down based on this logic, the next set of corrections appear on RecordIDs 203-210

5. Lastly, as an addition to Point #3 above, on RecordID=238 notice that we have OTHER on both CAT1 and CAT3 when CAT2 reads "ACT1" so based on our logic above, we find which CAT columns show the first instance of "ACT2" or "ACT3" or "ACT4" and in this case, it is CAT3 but only alter the rows which reads "OTHER" on CAT3 and leave "TAG" value unchanged on CAT3 column i.e. only change RecordIDs 238,239,240,241 as shown on the right section of the .xls.

Can someone from the community help me achieve this using a macro as this has to be repeated many times.
Thanks for your valuable time!

4 REPLIES 4
Qiu
20 - Arcturus
20 - Arcturus

@Altusr1 
I read twice, and still not able to comprehand the logic here.
let me try it again in this weekend. 😁

Altusr1
7 - Meteor

Ok @Qiu Thanks - if you have specific questions let me know but the key is when we look for ACT2/3/4 and when it happens first below a row where ACT1 happens, it can change on some row from CAT2 to CAT3 and only where these ACT2/3/4, we need to make corrections for all the rows above starting with the row where ACT1 happened.

 

 

JarekSkudrzyk
11 - Bolide

@Altusr1 

hi, I have managed to solve it without macros - please take a look and let me know if this works as intended on your data (it does on the given sample of 264 records).
Not the prettiest workflow, but hopefully it gets the job done:)

JarekSkudrzyk_0-1649715290670.png

 

JarekSkudrzyk
11 - Bolide

@Altusr1 

my previous workflow did not take into account the information you gave in point 2) regarding which column should be changed.
Please see updated workflow.

Labels