Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Find 7 th Consecutive worked Day

dkma
8 - Asteroid

Hi all, 

 

How to find if there are 7 consecutive working days in a Row. 

 

111118/01Yes
111118/02Yes
111118/03No
111118/04No
111118/05yes
111118/06Yes
111118/07Yes
111118/08Yes
111118/09Yes
111118/10Yes
111118/11Yes
111118/12Yes
111118/13yes
111118/14No
111118/15No
111118/16No
111118/17Yes
111118/18Yes
111118/19No

 

 

Kindly help

5 REPLIES 5
AngelosPachis
15 - Aurora

Hey @dkma ,

 

Can you elaborate a bit more on what the columns in the table stand for?

 

Is 8/01 a date? So the 1st of August? If yes, for which year? Also, what yes.no stands for? Is it the desired output, or is it a flag of whether that employee has worked that day or not?

mnmemilymnm
7 - Meteor

I take it that "Yes" means the person worked, and "No" means they didn't? Let's call that column [Worked].

 

I recommend using a multi row formula tool to create a ticker that starts with 1 on the first day worked, increases by 1 for every subsequent day worked, and resets back to 1 next time the person works after having taking some time off.

 

In the configuration of the Multi Row Formula tool, create a new field called Ticker. Set Value for Rows that Don't Exist to 0 or Empty. If you have multiple employees (more than just employee #1111), then Group By Employee.

 

The formula will look something like this:

 

if([Date]="No")

then 0

elseif(Row:[Worked]="Yes" and (Row-1:[Worked]="No" or Row-1:[Worked]=0))

then 1

elseif(Row-1:[Worked]="Yes")

then Row-1:[Ticker]+1

else Null()

endif

 

You may have to play with the formula a bit, but that's the general idea.

 

Then you can filter for [Ticker]>=7, and those will be the dates where the employee is on their 7th (or 8th or 9th, etc.) day of working.

jdunkerley79
16 - Nebula
16 - Nebula

I would suggest:

jdunkerley79_0-1632411739766.png

 

First, parse the dates

You can then filter the days not worked

Then final sort into ID, Date ascending

Finally a multi-row formula tool can confirm that the last 7 days were all worked for an ID:

jdunkerley79_1-1632411892612.png

 

Have attached a quick sample

 

 

 

apathetichell
16 - Nebula

Stylistically I don't ever use more than say 2 in the multi-row formula. I don't really know why.  I like a Tile tool approach/unique value approach.

Qiu
17 - Castor

@dkma 
Batch macro should be a good match for this case.

0921-dkma.PNG

Labels