Counting of Rows on Same Values
Hi, I have 2 tables Employee and Task. Each Employee has many Tasks and those Tasks are defined weekly. In the Task Table I am trying to capture the count of the tasks by Employee Name+Week Numb...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendLet us not give up on the summary report approach quite yet. My point of making a summary report is once you have that you can use the MORE button to copy these records to another table. Can you tell me if there is a reason that approach will not work for you?
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
Hi Mark,
I do not want any manual intervention but it should be automated thru pipelines or any other way.
Thanx
------------------------------
Gunsham Lakhiani
------------------------------
I do not want any manual intervention but it should be automated thru pipelines or any other way.
Thanx
------------------------------
Gunsham Lakhiani
------------------------------
- MarkShnier__YouIt can be done them using Formula Queries. You would need one Formula Query to identify the Record with the Minimum Record ID# for the Unique combination. Then another Formula Query to get the sum of the qty for those in the same "unique combination" of records.
It's beyond the scope of what I can do on this Forum to provide the code for the Formula Queries.
If you search this forum for Formula Query unique you will probably find some posting on that aspect. If you decide to go that route I can assist if you get stuck. But you would need to educate your self on Formula Queries first.
Or contact me directly by the email in my signature line if you want to take this off the Forum.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------- MarkShnier__YouIf you query and get a record set for the Record ID#'s (field ID# = 3) they will be returned in numeric sequence smallest first. The leftmost one will be the minimum.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------ - Make your "Count" field a Formula - Numeric field, then you can try something like this:
var text tableID = tid;
var text query = "{'fid1'.EX." & [Name] & "}AND{'fid2'.EX." & [Week] & "}AND{'fid3'.EX." & [Color] & "}AND{'3'.LTE." & [Record ID#] & "}";
Size(GetRecords($query, $tableID))
Where tid is the table ID where the source data is housed, fid1 is the field ID for the Name, fid2 is the field ID for Week and fid3 is the field ID for Color.
This should at least get you into the ballpark.
Then you would need a second query on the count field to return the max.
------------------------------
Paul Peterson
------------------------------- Hi Paul,
Thank you for the query. Now it shows me the count of same rows as 1, 2 and so on which is much better than what it was showing with my query as 2 and 2 on both the rows.
I would now like to have the higher count rows added to another table. Would that be possible thru a pipeline where it can check the Max Record id and push that record into the new table?
Not sure how to go about it.
Regards
Gunsham
------------------------------
Gunsham Lakhiani
------------------------------
