Count Unique Words in Table Array I have a ...

(Jose Raymundo) #1

Count Unique Words in Table Array

I have a table that is populated by rows of unique trucks hauling material in and out on any given day on any given job site. Multiple trucks (rows) will be created for the same date and job. The keys for these rows are currently uniquie ID’s. Multiple jobs will be logging multiple trucks on any given day. I need to count all of the materials hauled by one or more trucks for any given ‘Job - Date’. The goal is to count how many loads of concrete, or other materials, are used. There is a set enum list of material types to choose from so I simply need to report the counts of these materials.

I am leaning toward writing sheet expressions to report on a separate sheet (this is a challenge by itself) but want to see if any of you have any better ideas? Thanks in advance.

Example data below:

(Aleksi Alkio) #2

For concrete column (virtual column) you can use COUNT(SELECT(Table#1[OUT 1],AND([OUT 1]=“Concrete”,[JOB - DATE]=[_THISROW].[JOB - DATE])))+COUNT(SELECT(Table#1[OUT 2],AND([OUT 2]=“Concrete”,[JOB - DATE]=[_THISROW].[JOB - DATE])))+COUNT(SELECT(Table#1[OUT 3],AND([OUT 3]=“Concrete”,[JOB - DATE]=[_THISROW].[JOB - DATE])))

(Stephen Mattison) #3


Wow, nice, amazing, stupendous, inconceivable… Translate

(Aleksi Alkio) #4

Without knowing anything about the app structure I would use related table for punching. Then it would be easier to construct formulas. Another thing is that then it doesn’t matter how many in/out records you will have.