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_Date | days indicator1 | day indicator 2 | Desired Output |
10-10-1949 20:30 | Take | 1 | 1 |
10-10-1949 21:00 | Dont | 1 | |
10-10-1955 17:00 | Dont | ||
10-10-1956 21:00 | Dont | ||
10-10-1960 20:00 | Dont | ||
10-10-1961 19:00 | Dont | ||
10-10-1965 21:00 | Take | 1 | 1 |
10-10-1965 23:45 | Dont | 1 | |
10-10-1966 20:00 | Take | 1 | 1 |
10-10-1966 21:00 | Dont | 1 | |
10-10-1968 13:00 | Take | 1 | 1 |
10-10-1968 19:00 | Dont | 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.
Solved! Go to Solution.
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?
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