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

Changing Column Positions After Certain Occurence

kpontarollo
8 - Asteroid

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_0-1649425296375.png

 

19 REPLIES 19
mbarone
16 - Nebula
16 - Nebula

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).

binuacs
20 - Arcturus

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

kpontarollo
8 - Asteroid

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.

 

kpontarollo_0-1649427586401.png

 

Pingu
10 - Fireball

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.

kpontarollo
8 - Asteroid

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

mbarone
16 - Nebula
16 - Nebula

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

Pingu
10 - Fireball

I edited my post so now it  should work if it starts with a auto open.

Aguisande
15 - Aurora
15 - Aurora

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

Aguisande_0-1649432260315.png

 

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:

Aguisande_2-1649432883361.png

 

- Output Data:

Aguisande_3-1649432928903.png

 

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

kpontarollo
8 - Asteroid

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

Labels