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

How to update value of the previous row based on conditional statement?

sheidari
8 - Asteroid

How can I update/change the value of the previous row based on an IF statement? I'm using a multi-row formula tool to flag records that match on date (if date matches then 1 else 0) but it only flags the current record. I also want to flag the prior matching record as well.  

 

Is this possible and if so, how??

 

Thanks!!

 

Shahab

7 REPLIES 7
messi007
15 - Aurora
15 - Aurora

@sheidari,

 

Could you share a sample data with the expected output? It will help us to give you the expected solution.

 

Regards,

sheidari
8 - Asteroid

Here you go, @messi007 !

 

Current Output   
NameDateCodeSameMonthFlag
John Doe11/1/201811110
John Doe11/2/201811121
Jack Johnson12/1/201911000
Bob Jones2/1/202010240
Bob Jones2/1/202010271
    
Expected Output   
NameDateCodeSameMonthFlag
John Doe11/1/201811111
John Doe11/2/201811121
Jack Johnson12/1/201911000
Bob Jones2/1/202010241
Bob Jones2/1/202010271
messi007
15 - Aurora
15 - Aurora

@sheidari,

 

Here you go:

 

messi007_0-1615224414947.png

Attached the workflow,

 

If this helps please make it as a solution it will help others,

 

Regards,

danilang
19 - Altair
19 - Altair

Hi @sheidari 

 

Compare the current row to the row before and also the row after 

 

If datetimemonth(datetimeparse([date],"%m/%d/%Y")) = datetimemonth(datetimeparse([row-1:date],"%m/%d/%Y")) 
or 
   datetimemonth(datetimeparse([date],"%m/%d/%Y")) = datetimemonth(datetimeparse([row+1:date],"%m/%d/%Y"))  then
	1
else
	0
endif

 

 

Dan

sheidari
8 - Asteroid

@messi007  and @danilang thanks for the suggestions but maybe i didn't explain the problem clearly. 

 

Here's a better explanation. The raw data does not have the "samemonthflag" field.

 

Raw Data  
NameDateCode
John Doe11/1/20181111
John Doe11/1/20181112
Jack Johnson12/1/20191100
Bob Jones2/1/20201024
Bob Jones2/1/20201027


I created the following multi-row formula to create the samemonthflag field:

sheidari_2-1615230614440.png

 

But my output resulted in this: as you can see, I only want to flag individuals where they have a duplicate date (i.e. John Doe's 1st record should = 1 and so should Bob Jones 1st record. 

Current Output   
NameDateCodeSameMonthFlag
John Doe11/1/201811110
John Doe11/1/201811121
Jack Johnson12/1/201911000
Bob Jones2/1/202010240
Bob Jones2/1/202010271

 

The final result should look like this but i can't seem to figure out how to make the prior record a '1' if there's a duplicate date for that individual:

Expected Output   
NameDateCodeSameMonthFlag
John Doe11/1/201811111
John Doe11/1/201811121
Jack Johnson12/1/201911000
Bob Jones2/1/202010241
Bob Jones2/1/202010271
afv2688
16 - Nebula
16 - Nebula

Hello @sheidari ,

 

You can use the following formula:

 

IF [Row+1:Date] = [Date] OR [Date]=[Row-1:Date] THEN 1 ELSE 0 ENDIF

 

RegardsUntitled.png

messi007
15 - Aurora
15 - Aurora

@sheidari,

 

Oh my bad yes, I updated the formula accordingly

 

messi007_0-1615231835587.png

 

Attached the workflow,

 

Regards,

Labels