Create Row Number based on Multiple conditions

[Sai33]

Hello All,

I'm looking for inputs in creating a new Counter Field (Probably can be done with RowNo()) based on multiple other fields in Load Script part.

My Input data looks as below:

ID	Place	Entry	Exit
0B81432	SPAP	-	17.01.2019 12:45
0B99873	MUCG		18.01.2019 13:43
0B81432	CHSP	19.01.2019 08:35	-
0B81432	CHSP	-	24.01.2019 13:36
0B81432	SPAP	24.01.2019 13:36	-
0B81432	SPAP	-	18.03.2019 17:51
0B81432	SAVP	24.09.2019 09:09	-
0B81432	SAVP	-	26.09.2019 06:38
0B81432	CHSP	26.09.2019 06:38	-
0B81432	CHSP	-	04.10.2019 22:00
0B81432	BRVP	21.10.2019 09:04	-
0B81432	BRVP	-	27.02.2020 12:36
0B81432	BRVP	28.02.2020 09:19	-
0B81432	BRVP	-	08.03.2020 23:00
0B81432	DURP	12.04.2020 14:43	-
0B81432	DURP	-	15.04.2020 15:55
0B99873	DURP	12.04.2020 14:43	-
0B99873	DURP	-	19.04.2020 15:55
0B99873	SAVP	29.04.2020 15:55	-

Based on this, i would like to add a Counter for each ID, Place combination. 

My expected result would be the below table:

ID	Place	Entry	Exit	Event Number
0B81432	SPAP	-	17.01.2019 12:45	0
0B99873	MUCG		18.01.2019 13:43	0
0B81432	CHSP	19.01.2019 08:35	-	1
0B81432	CHSP	-	24.01.2019 13:36	1
0B81432	SPAP	24.01.2019 13:36	-	2
0B81432	SPAP	-	18.03.2019 17:51	2
0B81432	SAVP	24.09.2019 09:09	-	3
0B81432	SAVP	-	26.09.2019 06:38	3
0B81432	CHSP	26.09.2019 06:38	-	4
0B81432	CHSP	-	04.10.2019 22:00	4
0B81432	BRVP	21.10.2019 09:04	-	5
0B81432	BRVP	-	27.02.2020 12:36	5
0B81432	BRVP	28.02.2020 09:19	-	6
0B81432	BRVP	-	08.03.2020 23:00	6
0B81432	DURP	12.04.2020 14:43	-	7
0B81432	DURP	-	15.04.2020 15:55	7
0B99873	DURP	12.04.2020 14:43	-	2
0B99873	DURP	-	19.04.2020 15:55	2
0B99873	SAVP	29.04.2020 15:55	-	3

I've tried using the RowNo() option in Load Script but, i didn't workout as expected.

Any pointers would be highly appreciated. 

Thanks

Sai

[sunny_talwar · Accepted solution]

May be try this

Table:
LOAD * INLINE [
    ID, Place, Entry, Exit
    0B81432, SPAP, -, 17.01.2019 12:45
    0B99873, MUCG, -, 18.01.2019 13:43
    0B81432, CHSP, 19.01.2019 08:35, -
    0B81432, CHSP, -, 24.01.2019 13:36
    0B81432, SPAP, 24.01.2019 13:36, -
    0B81432, SPAP, -, 18.03.2019 17:51
    0B81432, SAVP, 24.09.2019 09:09, -
    0B81432, SAVP, -, 26.09.2019 06:38
    0B81432, CHSP, 26.09.2019 06:38, -
    0B81432, CHSP, -, 04.10.2019 22:00
    0B81432, BRVP, 21.10.2019 09:04, -
    0B81432, BRVP, -, 27.02.2020 12:36
    0B81432, BRVP, 28.02.2020 09:19, -
    0B81432, BRVP, -, 08.03.2020 23:00
    0B81432, DURP, 12.04.2020 14:43, -
    0B81432, DURP, -, 15.04.2020 15:55
    0B99873, DURP, 12.04.2020 14:43, -
    0B99873, DURP, -, 19.04.2020 15:55
    0B99873, SAVP, 29.04.2020 15:55, -
];

FinalTable:
LOAD *,
	 If(ID = Previous(ID), If(Place = Previous(Place), Peek('Event Number'), RangeSum(Peek('Event Number'), 1)), 0) as [Event Number]
Resident Table
Order By ID;

DROP Table Table;