Only counting unit of Sat and Sun that happen in a sequential order

[nezuko_kamado]

I need to count days of Sat and Sun units that happen consecutively.  If ID has unit of either Sat or Sunday, not both days, then I shouldn't count it because the analysis aims to see who works over weekend without break.

So first, I reshaped my dat by ID,  then assign 1  to Sat and Sun if both days have units. But I got errors as below

ID B of 10/10/2021 flag has to be 0, since previous day(Sat) 10/09/2021 doesn't have unit.

ID F of 10/16/2021 flag has to be 1, since both days(Sat and Sun) have unit.

I Paste the code and dat as below.

dat:

load * inline [ Date, Week,      ID, Unit

9/1/2021,                  1,               A,              1

9/1/2021,                  1,               B,              2

9/7/2021,                  2,               A,              3

9/7/2021,                  2,               C,              3

9/14/2021,                3,               A,              2

9/15/2021,                3,               B,              2

9/16/2021,                3,               D,              2

9/20/2021,                4,               A,              2

9/20/2021,                4,               D,              5

9/21/2021,                4,               B,              1

9/27/2021,                5,               A,              2

9/27/2021,                5,               F,               5

9/28/2021,                5,               D,              2

10/4/2021,                6,               A,              2

10/5/2021,                6,               B,              1

10/6/2021,                6,               C,              1

10/11/2021,             7,               A,              1

10/11/2021,             7,               C,              5

10/9/2021,                7,               A,                 1            

10/10/2021,             8,              B,             3               

10/11/2021,             8,               C,              2

10/11/2021,             7,               F,               2

10/12/2021,             8,               F,               1               

10/13/2021,             8,               A,              3               

10/14/2021,             8,               A,              4               

10/15/2021,             8,               B,              5               

10/16/2021,             8,               A,              5               

10/17/2021,             9,               A,             1

10/17/2021,             9,               A,              5

10/17/2021,             9,               A               

10/18/2021,             9,               C,              2               

10/19/2021,             9,               D,              3

10/16/2021,             8,               F,               3               

10/17/2021,             9,               F,               1

10/20/2021,             9,               F,              1];

reshape_dat:
LOAD
ID,
Date,
Week,
Sum(Unit) as Unit2
Resident dat
Group by ID,Date, Week;

Drop table dat;

WeekEnds:
LOAD *,
if (Unit2 > 0 and previous(Unit2) > 0 and Date - Previous(Date) = 1 and (WeekDay(Date) = 'Sat' or WeekDay(Date) = 'Sun'), 1,0) as WeekEnd_Flag

Resident reshape_dat;
drop Table reshape_dat;

[anthonyj · Accepted solution]

Hi @nezuko_kamado ,

The trickiest thing I find with these kinds of use cases is placing flags against both the Saturday and Sunday records as the use of "Previous" functions only place flags against the latter row. I've used an approach below that uses a join criteria with a key to overcome this.

The pick match function sets Saturday's date for the weekends regardless of it being Saturday (6) or Sunday (0). This column, along with the ID, will be used for linking the table below

dat:
LOAD
ID,
date(date#(Date,'MM/DD/YYYY'),'DD/MM/YYYY') as Date,
pick(match(num(weekday(date#(Date,'MM/DD/YYYY'))), 6, 0),
  date(date#(Date,'MM/DD/YYYY'),'DD/MM/YYYY') ,
date(date#(Date,'MM/DD/YYYY')-1 ,'DD/MM/YYYY')) as link,
Week,
Sum(Unit) as Unit2
resident dat
Group by ID,Date, Week
;

// The ID and link are a composite key in the join ensuring the flag is placed against both the Saturday and Sunday record.
// The "Previous" functions as you used in your code ensure that both the ID and link values have to match to load in these values

left join(dat)
Load
ID,
link,
1 as WeekendFlag
Resident dat
where ID = Previous(ID)
and link = previous(link)
;

// The last step is to clean up the column and place the "0" in a weekend column.
Output:
load
*,
alt(WeekendFlag,0) as Weekend_Flag
Resident dat;

drop Table dat;

I hope this helps with your query.

Thanks

Anthony