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

What is Alteryx version of "For" Loop: Comparing fields based on values of other columns

Lauren
7 - Meteor

Background

In my workflow I currently have these columns from the "input data" app from an excel spreadsheet:

  • [Program]
  • [Month]
  • [Year]
  • [Recovery Date]
  • [Performance Element]
  • [Performance Rating]

...and these via the "formula" app from user interface drop down or calculations:

  • [Report Month]
  • [Report Year]
  • [Report Date]
  • [Previous Report Date]
  • [Performance Color]

Note: [Month] and [Year] date back January 2016 so these go from (1/16, 2/16, 3/16,..., 3/18, 4/18, 5/18). Every month this excel spreadsheet is pulled and will insert the new month's data. I want to keep this data in the output to see changes over time, but only want to report the status of the [Report Date]. There are about 10 Performance Elements and 100 Programs for months and months of data.

 

Problem

I would like to loop through each [Performance Element] for each [Program] to determine if the [Performance Color] for a [Report Date] is the same as the [Performance Color] for the [Previous Report Date]. Basically I am wondering, How do I distinguish between the [Performance Color] from one month and the [Performance Color] from another month for different elements/programs without creating a ton of new variables?

 

Example

If I am pulling a report for May 2018, [Report Month] = 5, [Report Year] = 2018. In this example, the Status for Cost in May 2018 would say "No Change" because [Performance Color] went from Green to Green whereas Schedule would say "Change" because [Performance Color] went from Yellow to Green for May.

 

ProgramMonthYearPerformance ElementPerformance ColorStatus
A32018CostGreen(Null)
A32018ScheduleYellow(Null)
A42018CostGreen(Null)
A42018ScheduleYellow(Null)
A52018CostGreenNo Change
A52018ScheduleGreenChange

 

I really am just looking for a type of "For" or "Do While" loop here I think, but am confused with how Alteryx would do a loop without the ability in the formula app.

 

  • For Each [Program]
    • For Each [Performance Element]
      • If [Performance Color].[Report Date]=[Performance Color].[Previous Report Date] Then
        • [Status].[Report Date] = "No Change"
      • Elseif [Performance Color].[Report Date]<>[Performance Color].[Previous Report Date] Then
        • [Status].[Report Date] = "Change"
      • Else
        • "Error"
      • Endif

 ^^ I know this is a made-up language but this would be how I would think of it if it helps anyone understand what I'm going for.

 

Thanks to anyone who tries to help!

 

9 REPLIES 9
patrick_digan
17 - Castor
17 - Castor

@Lauren I would try the multi-row formula tool as shown in the attached v11.0 workflow. I sorted the data to ensure it was in the correct order, and then I used the group by feature of the multi-row formula tool. Hopefully that points you in the right direction!

BenMoss
ACE Emeritus
ACE Emeritus

I think this can be achived using a multi-row formula tool.

 

See attached.

 

Ben

 

Lauren
7 - Meteor

This is so helpful! How do I get it so I only see a status in the month of 5 though? You got it so that 3 is NODATA, but 4 still has Change/NoChange. I am confused where this is defined. What is "IsNull([Row-1: Program]) doing? Because the programs aren't going to be Null ever right? I appreciate the help.

BenMoss
ACE Emeritus
ACE Emeritus

So you only want to see the change when the month is the latest month?

 

i.e. for 5, but not for 4, even though 3 exists?

Lauren
7 - Meteor

I want to display all of the data, but only see the Status change from 4 to 5 displayed in the row for month 5. (see the table in the initial question)

BenMoss
ACE Emeritus
ACE Emeritus

New solution based on that feedback...(see attached)

 

if isnull([Row-1:Program]) then NULL()
elseif !isnull([Row+1:Program]) then NULL()
elseif [Row-1:Performance Color] = [Performance Color] then "No Change" else "Change" endif

For each program-performance element

 

if the row before is null() i.e. the first row then NULL

 

else where the row after is not null (i.e. there is a further month) then NULL

 

else do the calculation.

 

Ben

Lauren
7 - Meteor

Last question--

 

I am trying to troubleshoot because your solution does what I want mine to do, but I have other things going on in the workflow that I would not be able to post here. My values were all coming out as "No Change" and I think it's because I have a filter on in my workflow that filters on the current month. When I remove the filter, I get a "Change" or "No Change" for all months (not just month 5).

 

I am thinking about creating a variable that's the previous report month (aka if Report Month is 5, Previous Report Month would be 4) so that I can filter on current month and previous month maybe? But although I changed the name of a variable called [#1] to  [Report Month], when I try to make a variable called [Prev Report Month] and write in the configuration "[#1]-1" I get an error that says it's invalid. They are both doubles. Any ideas?

BenMoss
ACE Emeritus
ACE Emeritus

Hi Lauren,

 

I appreciate you cannot share your problem entirely but could you perhaps share the example which is causing the error (maybe just use a text input with a couple of lines in)?

 

Ben

Lauren
7 - Meteor

Here is a sample, thanks! Let me know if you have issues opening it.

Labels