Counting Rows in a Table

[tmumaw]

Hi All,

I have a quick question.  I need to count each row for an employee and reset the counter on the next employee.  How can I do this in my script.  For example:

employee ID      Date

000000001        3/2/2020            1

000000001        4/9/2020            2

000000002         1/10/2019         1

000000003          6/21/2020        1

Thanks

[sunny_talwar · Accepted solution]

You can use something like this

Table:
LOAD employeeID,
     Date
FROM ...;

FinalTable:
LOAD *,
     If(employeeID = Previous(employeeID), RangeSum(Peek('RowNum'), 1), 1) as RowNum
Resident Table;

DROP Table Table;

[AndrewHughes]

I guess I don't fully understand the goal here. If your goal is to have the count for an employeeID on each record for that corresponding employeeID. You can do
LEFT JOIN(Table)
LOAD
COUNT(employeeID) As [EmployeeID Count]
Resident [Table]
GROUP BY EmployeeID;
Then Join this table to your original table on EmployeeID.
If labeling in the order of records is the goal I would utilize a peek function.

[tmumaw]

Thanks Sunny.  Worked perfect.  The problem was we had the same employee with multiple service violation dates and we only wanted the first one.  So the rowno worked perfect resetting it for each employee.