Generate sequence numbers(1,2,3,etc) and reset it to 0 when is not in target

[0li5a3a]

Hello,

I need to create a new column where I generate a sequence number if the Target is met and if not I need to put 0 and start again to generate a sequence number.

id, Date, Target
1, 01/01/2020, 10
1, 02/01/2020, 0
1, 03/01/2020, 6
1, 04/01/2020, 10
1, 05/01/2020, 10
2, 01/01/2020, 0
2, 02/01/2020, 0
2, 03/01/2020, 10
2, 04/01/2020, 10
2, 05/01/2020, 10

......

I need something like this 

id, Date, Target, New Col
1, 01/01/2020, 10, 1
1, 02/01/2020, 0, 0
1, 03/01/2020, 6, 0
1, 04/01/2020, 10, 1
1, 05/01/2020, 10, 2
2, 01/01/2020, 0, 0
2, 02/01/2020, 0, 0
2, 03/01/2020, 10, 1
2, 04/01/2020, 10,2
2, 05/01/2020, 10, 3

.......

Thanks in Advance!

C

[Taoufiq_Zarra]

@0li5a3a  target is awlays 10 ?

[0li5a3a]

yes the target is 10 or above. thanks 

[Taoufiq_Zarra · Accepted solution]

@0li5a3a  so if target is always 10 its ok if not you can replace 10 in script with Value or your target column:

Input:

LOAD * INLINE [
id, Date, Target
1, 01/01/2020, 10
1, 02/01/2020, 0
1, 03/01/2020, 6
1, 04/01/2020, 10
1, 05/01/2020, 10
2, 01/01/2020, 0
2, 02/01/2020, 0
2, 03/01/2020, 10
2, 04/01/2020, 10
2, 05/01/2020, 10
];

Final:
noconcatenate

load *,if(rowno()=1,1,if(peek(id)=id and Target=10,peek(New_Col)+1,0)) as New_Col resident Input order by id,Date;


drop table Input;

output:

[0li5a3a]

Many thanks !!!

[0li5a3a]

@Taoufiq_Zarra , I mark the solution like is working but is not, the reason is if I have a new "id", e.g id= 3 and I have the target 10 the code is not putting an 1 in the New_Col.

I believe if I restrict the rowno() = 1 to put 1 is ignoring all the other ids with are new .

[Taoufiq_Zarra · Accepted solution]

@0li5a3a  you can change it to

Input:

LOAD * INLINE [
id, Date, Target
1, 01/01/2020, 10
1, 02/01/2020, 0
1, 03/01/2020, 6
1, 04/01/2020, 10
1, 05/01/2020, 10
2, 01/01/2020, 0
2, 02/01/2020, 0
2, 03/01/2020, 10
2, 04/01/2020, 10
2, 05/01/2020, 10
3,01/02/2020,10
3,02/02/2020,10
3,03/02/2020,10
3,04/02/2020,10
3,05/02/2020,10

];

Final:
noconcatenate

load *,if(rowno()=1,1,if(peek(id)=id and Target=10,peek(New_Col)+1,if(Target=10,1,0))) as New_Col resident Input order by id,Date;


drop table Input;

output:

or can you share the sample in Excel format