update the value based on the status group by the year

[Anastasio_Theohari]

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

[binuacs]

@Anastasio_Theohari One way of doing this

[Luke_C]

Hi @Anastasio_Theohari 

Try this:

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

[DataNath]

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: