topic Re: Counting Rows in a Table in QlikView

Counting Rows in a Table

tmumaw — Sat, 16 Nov 2024 01:10:13 GMT

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

Re: Counting Rows in a Table

sunny_talwar — Thu, 27 Feb 2020 20:52:53 GMT

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;

Re: Counting Rows in a Table

AndrewHughes — Thu, 27 Feb 2020 21:17:15 GMT

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.

Re: Counting Rows in a Table

tmumaw — Fri, 28 Feb 2020 11:51:06 GMT

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.