Summarising rows based on other columns

[JNelson]

Hello!

I need to summarise one of my columns [Qty] based on the other columns in the table.

If Tr and Type are blank, and if Date and Job No are the same, I need the Qty to sum and the populated Tr and Type to align with the summed amount, producing one single row for that entry.

Is this possible?

Thank you!

[ryan_mayu]

@JNelson 

you can do this in pq, here is a workaround for you . pls see the attahment below

[JNelson]

Thanks for your reply. I don't really know how to apply this to my current data set/tables. Do you have any commentary that explains what is happening?

Thanks

[ryan_mayu]

separate the table, one includes the data without null type. another includes the data with null type. 

Then group by the second table. At last, combine two tables

[JNelson]

Thanks for your efforts, but when I put the whole data set in (not just the sample you took) it doesn't work. There are some jobs that are taking the number out of the tr column and that looks to be effecting the Qty column. Or there are times when it's taking the number out of the tr column but it's not effecting the Qty. Perhaps it's to do with the job numbers?

Thanks 🙂

[ryan_mayu]

pls paste the sample data ,not the screenshot.

[JNelson]

Hi @ryan_mayu 

Below is the data table 

Thanks

Date	Job No	Qty	Tr	Type
27/02/2024	7KE0E11	673	180	Trucking
27/02/2024	7KE0P33	233	100	Trucking
27/02/2024	7KLC413	380	180	Profiling
27/02/2024	7KLC414		60	Asph
27/02/2024	7KLC414	121	2	Twn Pro
28/02/2024	7KE0351	535
28/02/2024	7KE0351	410	220	LB
28/02/2024	7KE0376		1	LB
28/02/2024	7KE0376	35	1	Twn Pro
28/02/2024	7KE0377	205	150	Pro
28/02/2024	7KE0377	183
28/02/2024	7KE0E11	53	1	LB
28/02/2024	7KE0E11	10	1	Twn
28/02/2024	7KLC413	382	180	Profiling
28/02/2024	7KLC414	38	60	Trucking
28/02/2024	7KLC414	106	2	Twn Pro
29/02/2024	62750	35	1	Twn
29/02/2024	7KE0351	570
29/02/2024	7KE0351	280	220	LB
29/02/2024	7KE0377	274
29/02/2024	7KE0377	198	180	Profiling
29/02/2024	7KE0P34	148
29/02/2024	7KE0P34	200	100	Trucking
29/02/2024	7KL0G65	372	125	Tan
29/02/2024	7KLC413	380	180	Profiling
1/03/2024	7KE0351	200	100	LB
1/03/2024	7KE0351	130
1/03/2024	7KE0P34	180	100	Trucking

[ryan_mayu · Accepted solution]

@JNelson 

pls see the attachment 

[JNelson]

Amazing! Thank you so much 🙂

[ryan_mayu]

you are welcome