Forum Discussion
Solved
Listing and counting unique values that match criteria
I want to create a list off another list but i'm having some issues getting things the way I want. The "All Task" is the list i'm pulling data from. I want to grab each unique Wave Number and th...
You have a few options here. I'd go with GROUPBY because it will allow you to apply multiple functions to the data. You may have to change the arrangement, but it handles this task well.
=LET( row_field, Tasks[Wave '#], f, row_field = rpt_filter, values, HSTACK(Tasks[Task '#], Tasks[Task '#], Tasks[Task Status]), fn, HSTACK(COUNTA, ARRAYTOTEXT, ARRAYTOTEXT), agg, GROUPBY(row_field, values, fn, , 0, , f), DROP(agg, 1) )
SQL:
select * from AllTasks limit 5;
select f02 as 'Insert Wave Number Here
',group_concat(distinct(f04)),count(distinct(f04)),f05,f07 from AllTasks group by f02;
f01 f02 f03 f04 f05 f06 f07 f08 f09
| 070 | 20241004038 | 70 WMAL WALMART | 1116144168 | Replen CASE-ROP | 40 | 00-Available | 1 | 20241004 |
| 070 | 20241004039 | 70 WMAL WALMART | 1116144178 | Replen CASE-ROP | 40 | 00-Available | 1 | 20241004 |
| 070 | 20241004065 | 70 WMAL WALMART | 1116144371 | Case Pick | 40 | 00-Available | 1 | 20241004 |
| 070 | 20241004052 | 70 WMAL WALMART | 1116144293 | Replen CASE-ROP | 40 | 00-Available | 1 | 20241004 |
| 070 | 20241004052 | 70 WMAL WALMART | 1116144294 | Replen CASE-ROP | 50 | 00-Available | 1 | 20241004 |
Insert Wave Number Here group_concat(distinct(f04)) count(distinct(f04)) f05 f07
| 20241004032 | 1116144124 | 1 | Replen CASE-ROP | 15-Held |
| 20241004033 | 1116144139 | 1 | Case Pick | 15-Held |
| 20241004034 | 1116144143 | 1 | Replen CASE-ROP | 20-Being Pulled |
| 20241004038 | 1116144168 | 1 | Replen CASE-ROP | 00-Available |
| 20241004039 | 1116144178 | 1 | Replen CASE-ROP | 00-Available |
| 20241004040 | 1116144183 | 1 | Less Than Full Pallet Replen CASE - RRC | 20-Being Pulled |
| 20241004041 | 1116144199 | 1 | Less Than Full Pallet Replen CASE - RRC | 15-Held |
| 20241004042 | 1116144208,1116144213 | 2 | Replen CASE-ROP | 20-Being Pulled |
| 20241004043 | 1116144220 | 1 | Replen CASE-ROP | 15-Held |
| 20241004044 | 1116144251,1116144248 | 2 | Replen CASE-ROP | 10-Prt/Assigned |
| 20241004046 | 1116144263 | 1 | Case Pick | 10-Prt/Assigned |
| 20241004047 | 1116144265 | 1 | Less Than Full Pallet Replen CASE - RRC | 15-Held |
| 20241004048 | 1116144269 | 1 | Replen CASE-ROP | 20-Being Pulled |
| 20241004052 | 1116144293,1116144294,1116144295,1116144289 | 4 | Replen CASE-ROP | 00-Available |
| 20241004053 | 1116144301 | 1 | Case Pick | 20-Being Pulled |
| 20241004056 | 1116144310,1116144315 | 2 | Less Than Full Pallet Replen CASE - RRC | 10-Prt/Assigned |
| 20241004058 | 1116144325 | 1 | Replen CASE-ROP | 15-Held |
| 20241004059 | 1116144335,1116144336 | 2 | Replen CASE-ROP | 20-Being Pulled |
| 20241004060 | 1116144339 | 1 | Case Pick | 15-Held |
| 20241004061 | 1116144341 | 1 | Less Than Full Pallet Replen CASE - RRC | 00-Available |
| 20241004062 | 1116144350,1116144349 | 2 | Replen CASE-ROP | 10-Prt/Assigned |
| 20241004064 | 1116144363 | 1 | Case Pick | 15-Held |
| 20241004065 | 1116144371 | 1 | Case Pick | 00-Available |
| 20241004066 | 1116144396,1116144395,1116144398 | 3 | Replen CASE-ROP | 00-Available |
| 20241004067 | 1116144406 | 1 | Case Pick | 15-Held |
| 20241004070 | 1116144421 | 1 | Less Than Full Pallet Replen CASE - RRC | 20-Being Pulled |
| 20241004072 | 1116144438,1116144437 | 2 | Case Pick | 10-Prt/Assigned |
Can't run SQL
- Why not.