Changing Column Positions After Certain Occurence

@kpontarollo

Here are my 2 cents:

I totally agree with @mbarone about the Multi-Row formula and how to allocate the time.

In this case, this is a 2 tool logic, being the Multi-Row Formula the core of the solving.

As you may see here, there are some other things added (like sorting the records prior of processing) to ensure that the use case is solved, no matter how you get the data (i.e. What happens when I receive scrambled data? What may happen if I get gaps between dates? .. etc..). Here, "Defensive Design" is your best friend.

The only use case it won't solve is if you have "Auto Opens" are not consecutive.

- Input Data:

- Output Data:

BTW: Just out of curiosity, shouldn't be the last Auto Open the one that remains as 1 in your Data? In case it's the other way around, only changing a sign will accomplish it (Yes, that tool is THAT powerfull!)

All comments

Hi @kpontarollo ,

Have you tried the Multi-Row Formula tool (link taken from the Tool Mastery Index , which you should definitely bookmark). Also, if you click on that tool on the Preparation tool pane, you'll see you can open an example of how to use the tool.

Try it out and see if you can't come up with a multi-row formula to get this done. Let us know once you give it a few tries if you can't seem to get it (show us the formula(s) you tried).

binu_acs

@kpontarollo Please provide the sample input file and expected output result as well in an excel?

Hi!

This would be the input vs the output (input on the left, output on the right). It would be on a much bigger scale with multiple people listed.

Pingu

Hi @kpontarollo

2nd try. What about this with the tiling tool?

Hmm I tried to change the numbers, but it is still wrong. I will try some more as it is an interesting one.

Edit: What about this. It probably can be done in a lot less tools.

Tilingv3.yxmd

@Pingu The tiling tool worked great for 98% of what I needed but just caused that one error- I appreciate you trying, I have been STUMPED for days.

@kpontarollo - I strongly suggest you familiarize yourself with the Multi-Row Formula tool as I previously mentioned. If you've been stumped for days, it will be well worth another half-day of really learning that tool. Not only will it solve this problem with one tool, but it will come in handy in countless future endeavors. Like I said, give it a go and if you get stuck show us what you've tried and we can help you through it.

@kpontarollo

Here are my 2 cents:

I totally agree with @mbarone about the Multi-Row formula and how to allocate the time.

In this case, this is a 2 tool logic, being the Multi-Row Formula the core of the solving.

The only use case it won't solve is if you have "Auto Opens" are not consecutive.

- Input Data:

- Output Data:

@mbarone definitely have started looking at this tool. I have began to play around with it- still no expert on it yet! I appreciate the suggestion so much! I would have never thought to use this tool. Thanks!

@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!

Very welcome!

@kpontarollo

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

@Pingu WOW THIS WORKED! Thank you so much!

@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!

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!