update the value based on the status group by the year

Question

Hello team,

I would like to update all the months that are in a specific status for each year..

lets say i have the below dataset:

YYYYMM  Status

202101        1

202102        1

202103        1

202104        1

202105        1

202106        1

202107        0

202108        1

202109        1

202110        1

202111        1

202112        1

202201       1

202202        1

if i found at least one status=0 in one month i would like to make all the others equal with 0..

So the result i would like to have is :

YYYYMM  Status

202101        0

202102        0

202103        0

202104        0

202105        0

202106        0

202107        0

202108        0

202109        0

202110        0

202111        0

202112        0

202201        1

202202        1

Any idea?

Thank you

DataNath · Answer

Hey @Anastasio_Theohari is this only ever 0 and 1? Or can there be other statuses? Just in case, I've built out the following:

1) Parse the Year:

2) Find the min value for each year:

3) Join back to main dataset based on year:

4) If the min_status is 0 then make the status 0, else leave it as is:

If the statuses can only be 0 or 1 then you can simplify this by just using the min_status field as your new status field within the Join:

Update status based on whole year.yxmd

Luke_C · Answer

Hi @Anastasio_Theohari

Try this:

I parsed out the year, found the minimum value, then joined it back and used that as the status.

New Workflow3.yxmd

binu_acs · Answer

@Anastasio_Theohari One way of doing this

Status_110423.yxmd