Changing Column Positions After Certain Occurence

[kpontarollo]

Hello Everyone, I tried to ask this question with no luck and I am hoping that a different way of explaining it is possible. Please see picture for reference. There can be as many "Total Opens" for one specific person, once the "Auto Open" happens for a specific person, the rest must become "Total Opens." Below, for Kelsey, the red "1" should be in the Total open column because after the first "auto- open" all must become total opens, for that specific person only. Under Kelsey, you can see Robyn, which has an accurate way of how it should be done. It does not matter that Total Opens came before the Auto Opens, just once that auto open does occur for that specific person, I need a formula that would change all other auto opens, to total opens. 

THANK YOU!

[kpontarollo]

@Aguisande that would make sense! But in this case, it is the first auto-open that needs to be counted and nothing after that. I appreciate this input! I am going to give it a try- thank you so much!

[kpontarollo]

@Pingu awesome- goin to try it out!

[mbarone]

Very welcome!

[Aguisande]

@kpontarollo 

So, Instead of comparing to the next Row, you need to compare with the previous row, and that's it.

[atcodedog05]

Hi @kpontarollo 

This is an interesting usecase. Here is my take on this. And I am solving it a bit differently without multi-row formula since this will be easy for me to explain and easy for others to understand the logic.

Just a suggestion from my end before building a workflow build the logic for it either in excel or as steps. Each step should be such a way that it can be implemented (if not divide into more smaller steps that can be implemented)

As per your explanation 

Pre Step: Implement the defensive design step mentioned by @Aguisande. Convert the date to Alteryx date, apply sort by person and sort by Alteryx date ascending.

1st Step: The logic you want to apply is on the rows where Auto Open is 1. So you can use a filter to keep where  Auto Open is 1

2nd Step: We only want to keep the first row of Auto Open as it is. And for rest Total Open should be 1 and Auto Open Null. How you can convert to Alteryx logic is by using unique tool that way first row and rest rows will be separated.

3rd Step: For rest Total Open should be 1 and Auto Open Null

4th Step: In excel it would have been clear the filter. Here its merge all the data back together using Union tool.

5th Step: Sort the records based on the person and Alteryx date descending.

Note: this also handles non-consecutive entries.

Hope I was clear if not please let me know.

Hope you learnt on how to build logic for usecases. Once you have done that it's easy to translate it in Alteryx and also easy to explain it to ops team & stake holders.

And as @mbarone suggested its always a good idea to get familiarized with the tools available. You can go through interactive lessons and explore possibilities in Alteryx.

https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons

Hope this helps : )

[atcodedog05]

Hi @kpontarollo 

Also If you are asking me can this same logic be implemented using Multi-row formula tool answer is yes. Its bit complex but possible i will let you figure that one out 🙂.

[kpontarollo]

@Pingu WOW THIS WORKED! Thank you so much!

[kpontarollo]

@atcodedog05 @Aguisande @mbarone @Pingu Thank you all so much for taking the time and giving input! I am so impressed and grateful for the alteryx community. I consistently am impressed with how many ways there are to tackle this. When it comes to alteryx, I feel very confident in the tools I am familiar with, but there is always a learning curve when it comes to things you have not done before and I am so thankful for all the help! I still have to go through some of the ideas offered on the page, and will accept as a solution once I am done trying them!

THANK YOU ALL SO MUCH!

[Aguisande]

Glad to be of help! And is so rewarding that you took the time to close this post like you did, so THANK YOU!

Actually don't loose the driver to keep learning (We all are still learning), and this journey never ends. With this in minds, you'll accomplish amazing things!

Cheers!

[mbarone]

What @Aguisande said!