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

Multi Row Formula

Hi,

 

I am trying to create the workflow in such a way that in case the current row in [days indicator 1] = "Take" and the next  row is "Dont", the output under [day indicator 2] should be "1" for both the rows. However, I am not able to achieve the same with the multi row formula tool. Attached is the data for your reference. 

 

Sighting_Datedays indicator1day indicator 2Desired Output
10-10-1949 20:30Take11
10-10-1949 21:00Dont 1
10-10-1955 17:00Dont  
10-10-1956 21:00Dont  
10-10-1960 20:00Dont  
10-10-1961 19:00Dont  
10-10-1965 21:00Take11
10-10-1965 23:45Dont 1
10-10-1966 20:00Take11
10-10-1966 21:00Dont 1
10-10-1968 13:00Take11
10-10-1968 19:00Dont 1

 

Kindly guide me in how we can achieve the desired output using alteryx. Any help would be greatly appreciated. Attaching the workflow for your reference as well.

 

 

4 REPLIES 4
PhilipMannering
16 - Nebula
16 - Nebula

It sounds like you only care if [day indicator1] is 'Take' or [row-1:day indicator1] is 'Take' (i.e. doesn't matter if current row is dont or take if the previous row is take. So this should work in a multirow,

 

if [days indicator1] = 'Take' 
or [Row-1:days indicator1] = 'Take' 
then 1 else 0 endif

 

Or am I missing something?

Thableaus
17 - Castor
17 - Castor

HI @Dinavahi_Abhishek 

 

Look at your original formula:

 

IF (([days indicator1] = "Take" AND [Row+1:days indicator1] = "Dont") OR ([days indicator1] = "Take" AND [Row+1:days indicator1] = "Take")) THEN "1" ELSE "" ENDIF

 

The first part is ok, but the second part should be like this:

IF (([days indicator1] = "Take" AND [Row+1:days indicator1] = "Dont") OR ([days indicator1] = "Dont" AND [Row-1:days indicator1] = "Take")) THEN "1" ELSE "" ENDIF

 

You just reverse the logic applied to the "Dont" Row, then you should be fine.

 

Cheers,

Hi Philip,

 

Thank you for your response. As per my understanding, multirow formula populates a value only for the active row. So in the case I have given, my basic motive is to filter in all rows where the current row and the succeeding row have the same date. 

So in the sample data I have provided, I am trying to populate "1" for the first two rows, since they have the same dates. Hence, I want to populate the value "1" under [day indicator 2] wherever there is a combination of "take" consecutively followed by "Dont" under the column [days indicator 1].  The same follows for row 8 and so on. 

Once i manage to get "1's" in this format, I plan on using the filter tool to filter in these dates. 

Kindly let me know if you need any more clarity on this matter. Also, please suggest if there is a better method to perform this analysis. 

Hi, 

 

Thanks for your response. The logic worked.

 

However, with the new logic, the system was not able to capture instances where the same date occurred consecutively for multiple rows or [days indicator 1] had the value "take" for multiple rows. So i modified the formula a bit and added a third logic so that all the cases were covered. The formula is as follows. 

 

IF (([days indicator1] = "Take" AND [Row+1:days indicator1] = "Dont") OR

([days indicator1] = "Take" AND [Row+1:days indicator1] = "Take") OR

([days indicator1] = "Dont" AND [Row-1:days indicator1] = "Take")) THEN "1" ELSE "" ENDIF

Labels