Flag with 3 Consecutive Values = 0

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

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!!!

Re: Flag with 3 Consecutive Values = 0

sunny_talwar — Tue, 21 Mar 2017 15:47:13 GMT

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;

topic Re: Flag with 3 Consecutive Values = 0 in QlikView

Flag with 3 Consecutive Values = 0

Re: Flag with 3 Consecutive Values = 0