We are celebrating the 10-year anniversary of the Alteryx Community! Learn more and join in on the fun here.
Start Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Value repeating, keeping only one rest returns 0

max_hfr
8 - Asteroid

Hi Guys,

 

I am struggling with some data transfromation...

 

I have made a sample workbook so it is easier to explain. I put a screenshot aswell of the file so you can directly use it.

 

max_hfr_1-1584707473222.png

 

 

Story: Imagine I am trying to follow some sales representatives' work. They enter in a software every single interaction they have with one of our customers and if they go on holliday they'll have to enter the days they went on hollidays.

 

Rows: 

Each row contains etither a visit of a client, a day of holliday or both

 

When there is a full day of holliday it will return 1 and therefore there can be no client interaction on that day and no unique ID(client ID) (ROW 9)

 

The problem occurs when our sales representative enters half a day of hollidays (0,5) in the morning and then interactions in the afternoon (in this case 4 interactions on the 28th). In that case the line repeats 0,5 days 4 times and therefore when I sum it up  it will return 2 days instead of 0,5

 

What I need is to be able to keep only one time the 0,5 and return 0 for the other 0,5

Or 

Divide the 0,5 per the number of interactions on the same day (in this case 0,5 / 4) so 0,125 for every row on the 28th

 

Here is a screenshot of both possible results I would like to have:

 

max_hfr_3-1584708083049.png

 

max_hfr_2-1584708051645.png

 

Thank you guys in advance!

 

 

4 REPLIES 4
PhilipMannering
16 - Nebula
16 - Nebula

Hi Max,

 

Something like this?

 

PhilipMannering_0-1584708870343.png

 

AbhilashR
15 - Aurora
15 - Aurora

@max_hfr - building on @PhilipMannering 's approach, you could use a multi-row formula to retain the first On Holiday and convert others to 0. 

 

Hope this helps.

max_hfr
8 - Asteroid

Hi,

 

First of all thank you very much to you both that was perfect.

 

However after further investigation I realized that if I have to make filters on customers further on I will loose the 0,5 for the rows where I deleted it.

 

 

was wondering if there is a way to actually add a row so the result would look like that

max_hfr_0-1584716494340.png

 

AbhilashR
15 - Aurora
15 - Aurora

@max_hfr - does something like the attached approach work?

 

 

Labels
Top Solution Authors