Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
tmumaw
Specialist II
Specialist II

Counting Rows in a Table

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

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

3 Replies
sunny_talwar

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
Partner - Contributor III
Partner - Contributor III

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
Specialist II
Specialist II
Author

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.