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

Looping through rows

jpbonilla
6 - Meteoroid

Hello. 

 

In my workflow, I have ranked the DATE field grouped by ID using Multi-Row Formula.  I need to populate the QUALIFIED field with "Yes" if the next ranked date is over 30 days from the previous qualified date.  Rank 1 will always be qualified.  Please see example below.

 

Data:

IDDATERANK
XXXXX9-Jan-20191
XXXXX29-May-20192
XXXXX26-Jun-20193
XXXXX21-Aug-20194
XXXXX16-Oct-20195
XXXXX20-Nov-20196
XXXXX21-Nov-20197
yyyyy6-Apr-20191
yyyyy30-Apr-20192
yyyyy1-May-20193
yyyyy1-Jun-20194
yyyyy4-Jun-20195
yyyyy1-Jul-20196
ZZZZZ2-Aug-20191
ZZZZZ13-Aug-20192
ZZZZZ6-Oct-20193

 

 

Result:

IDDATERANKQUALIFIED
XXXXX9-Jan-20191Yes
XXXXX29-May-20192Yes
XXXXX26-Jun-20193 
XXXXX21-Aug-20194Yes
XXXXX16-Oct-20195Yes
XXXXX20-Nov-20196Yes
XXXXX21-Nov-20197 
yyyyy6-Apr-20191Yes
yyyyy30-Apr-20192 
yyyyy1-May-20193 
yyyyy1-Jun-20194Yes
yyyyy4-Jun-20195 
yyyyy1-Jul-20196Yes
ZZZZZ2-Aug-20191Yes
ZZZZZ13-Aug-20192 
ZZZZZ6-Oct-20193Yes

 

Thank you.

6 REPLIES 6
fmvizcaino
17 - Castor
17 - Castor

Hi @jpbonilla ,

 

I've used a multi-row tool to calculate the month difference between dates from the same ID (group by ID) and then a formula to fill the qualified column.

 

Let me know if this works for you.

Best,

Fernando Vizcaino

jpbonilla
6 - Meteoroid

 

Thank you for your help @fmvizcaino !  I changed the date difference from Month to Day, and tried to calculate the days from previous qualified date (not previous date) in order to get the next qualified date.  Would appreciate your help on the part where Date is at least 30 days from previous qualified date.  Please see below.  Thank you.

 

IDRANKDateTime_OutDay DifferenceDay Difference (Correct)QualifiedQualified (Correct)
XXXXX11/9/2019  Yes 
XXXXX25/29/2019140 Yes 
XXXXX36/26/201928   
XXXXX48/21/20195684Yes 
XXXXX510/16/201956 Yes 
XXXXX611/20/201935 Yes 
XXXXX711/21/20191   
ZZZZZ18/2/2019  Yes 
ZZZZZ28/13/201911   
ZZZZZ310/6/20195465Yes 
yyyyy14/6/2019  Yes 
yyyyy24/30/201924   
yyyyy35/1/20191   
yyyyy46/1/201931 Yes 
yyyyy56/4/20193   
yyyyy67/1/20192730 Yes

 

fmvizcaino
17 - Castor
17 - Castor

Hi @jpbonilla ,

 

Solution attached. I needed to insert two more multi-row tools to make that happen.

 

Best,

Fernando Vizcaino

jpbonilla
6 - Meteoroid

Thank you again, @fmvizcaino .  I apologize I wasn't clear in my reply.  I didn't mean to add more columns.  Hoping your help for the expected result attached considering the calculation for the DayDifference will be from the last Qualified date as originally requested.

fmvizcaino
17 - Castor
17 - Castor

Hi @jpbonilla ,

 

Sorry for taking so long to answer.

Here is a new version of the workflow.

 

Take a look and let me know if this works for you.

Best,

Fernando Vizcaino

jpbonilla
6 - Meteoroid

No worries, @fmvizcaino.

 

This is perfect and very much appreciated!!!

Labels