Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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;

View solution in original post

AndrewHughes
Partner
Partner

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.