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.

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
16 - Nebula

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
8 - Asteroid

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
ACE Emeritus
ACE Emeritus

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
18 - Pollux

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
20 - Arcturus
20 - Arcturus

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

0921-dkma.PNG

Labels