Flag with 3 Consecutive Values = 0

[microwin88x]

Hello,

I have the following table:

ID	Period	Value
1	201501	300
1	201502	500
1	201503	800
1	201504	0
1	201505	0
1	201506	0
2	201301	100
2	201302	100
2	201303	100
3	201612	100
3	201701	0
3	201702	0
3	201703	0
4	201701	0
4	201702	0

Is there any way to add a column as expression with a flag having 3 consecutive Values = 0? Like this:

ID	Period	Value	Filter
1	201501	300	0
1	201502	500	0
1	201503	800	0
1	201504	0	1
1	201505	0	1
1	201506	0	1
2	201301	100	0
2	201302	100	0
2	201303	100	0
3	201612	100	0
3	201701	0	1
3	201702	0	1
3	201703	0	1
4	201701	0	0
4	201702	0	0

Thank you!!!

[sunny_talwar · Accepted solution]

May be like this

Table:

LOAD ID,

  Date(Date#(Period, 'YYYYMM'), 'YYYYMM') as Period,

  Value;

LOAD * INLINE [

    ID, Period, Value

    1, 201501, 300

    1, 201502, 500

    1, 201503, 800

    1, 201504, 0

    1, 201505, 0

    1, 201506, 0

    2, 201301, 100

    2, 201302, 100

    2, 201303, 100

    3, 201612, 100

    3, 201701, 0

    3, 201702, 0

    3, 201703, 0

    4, 201701, 0

    4, 201702, 0

];

Left Join (Table)

LOAD ID,

  Date(AddMonths(Period, -1), 'YYYYMM') as Period,

  Value as Value2

Resident Table;

Left Join (Table)

LOAD ID,

  Date(AddMonths(Period, -2), 'YYYYMM') as Period,

  Value as Value3

Resident Table;

TempTable:

LOAD ID,

  Period,

  Value,

  If(ID = Previous(ID), If(Value = 0 and (Previous(Flag) = 1 or Previous(Previous(Flag)) = 1), 1, Flag), Flag) as Flag;

LOAD ID,

  Period,

  Value,

  If(Value + Value2 + Value3 = 0, 1, 0) as Flag

Resident Table

Order By ID, Period;

DROP Table Table;