hi there - am working on Sales table with COGS data within as below here; am trying to get Brand name with High sales value for INV & where Sales value is "zero" & Purchase is "greater" than "Zero"
i.e. line 3 should show "Brand" as "ABC" instead of "Misc - HW"
[TABLE="width: 982"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Date[/TD]
[TD]Num[/TD]
[TD]Item[/TD]
[TD]Account[/TD]
[TD]Qty[/TD]
[TD]Sales Price[/TD]
[TD]Amount[/TD]
[TD]Balance[/TD]
[TD]Product[/TD]
[TD]Brand[/TD]
[TD]SKU[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]ABC:Chassis123[/TD]
[TD]Sales - Hardware[/TD]
[TD]1[/TD]
[TD] 1,300.00[/TD]
[TD] 1,300.00[/TD]
[TD] 1,300.00[/TD]
[TD]ABC[/TD]
[TD]ABC[/TD]
[TD]Chassis123[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]ABC:Chassis123[/TD]
[TD]Purchase - Hardware for Resale[/TD]
[TD]-1[/TD]
[TD] -[/TD]
[TD] (479.32)[/TD]
[TD] 820.68[/TD]
[TD]ABC[/TD]
[TD]ABC[/TD]
[TD]Chassis123[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Intel:I77700K[/TD]
[TD]Sales - Hardware[/TD]
[TD]1[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 820.68[/TD]
[TD]Intel[/TD]
[TD]Misc HW[/TD]
[TD]I77700K[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Intel:I77700K[/TD]
[TD]Purchase - Hardware for Resale[/TD]
[TD]-1[/TD]
[TD] -[/TD]
[TD] (124.00)[/TD]
[TD] 696.68[/TD]
[TD]Intel[/TD]
[TD]Misc HW[/TD]
[TD]I77700K[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Logitech:MU-26[/TD]
[TD]Sales - Hardware[/TD]
[TD]1[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 696.68[/TD]
[TD]Logitech[/TD]
[TD]Misc HW[/TD]
[TD]MU-26[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Logitech:MU-26[/TD]
[TD]Purchase - Hardware for Resale[/TD]
[TD]-1[/TD]
[TD] -[/TD]
[TD] (2.00)[/TD]
[TD] 694.68[/TD]
[TD]Logitech[/TD]
[TD]Misc HW[/TD]
[TD]MU-26[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Logitech:K241[/TD]
[TD]Sales - Hardware[/TD]
[TD]1[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 694.68[/TD]
[TD]Logitech[/TD]
[TD]Misc HW[/TD]
[TD]K241[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Logitech:K241[/TD]
[TD]Purchase - Hardware for Resale[/TD]
[TD]-1[/TD]
[TD] -[/TD]
[TD] (3.00)[/TD]
[TD] 691.68[/TD]
[TD]Logitech[/TD]
[TD]Misc HW[/TD]
[TD]K241[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Memory:8GB[/TD]
[TD]Sales - Hardware[/TD]
[TD]2[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 691.68[/TD]
[TD]Memory[/TD]
[TD]Misc HW[/TD]
[TD]8GB[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Memory:8GB[/TD]
[TD]Purchase - Hardware for Resale[/TD]
[TD]-2[/TD]
[TD] -[/TD]
[TD] (78.28)[/TD]
[TD] 613.40[/TD]
[TD]Memory[/TD]
[TD]Misc HW[/TD]
[TD]8GB[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Microsoft:WIN7PRO[/TD]
[TD]Sales - Software[/TD]
[TD]1[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 613.40[/TD]
[TD]Microsoft[/TD]
[TD]Misc HW[/TD]
[TD]WIN7PRO[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Microsoft:WIN7PRO[/TD]
[TD]Purchase - Software for Resale[/TD]
[TD]-1[/TD]
[TD] -[/TD]
[TD] (42.00)[/TD]
[TD] 571.40[/TD]
[TD]Microsoft[/TD]
[TD]Misc HW[/TD]
[TD]WIN7PRO[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Misc - HW:Z270[/TD]
[TD]Sales - Hardware[/TD]
[TD]1[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 571.40[/TD]
[TD]Misc - HW[/TD]
[TD]Misc HW[/TD]
[TD]Z270[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Misc - HW:Z270[/TD]
[TD]Purchase - Hardware for Resale[/TD]
[TD]-1[/TD]
[TD] -[/TD]
[TD] (62.14)[/TD]
[TD] 509.26[/TD]
[TD]Misc - HW[/TD]
[TD]Misc HW[/TD]
[TD]Z270[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]MPN:XVC00-PB[/TD]
[TD]Sales - Hardware[/TD]
[TD]1[/TD]
[TD] 130.00[/TD]
[TD] 130.00[/TD]
[TD] 639.26[/TD]
[TD]MPN[/TD]
[TD]MPN[/TD]
[TD]XVC00-PB[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]MPN:XVC00-PB[/TD]
[TD]Purchase - Hardware for Resale[/TD]
[TD]-1[/TD]
[TD] -[/TD]
[TD] (71.52)[/TD]
[TD] 567.73[/TD]
[TD]MPN[/TD]
[TD]MPN[/TD]
[TD]XVC00-PB[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Sam:512BW[/TD]
[TD]Sales - Hardware[/TD]
[TD]1[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 567.73[/TD]
[TD]Sam[/TD]
[TD]Misc HW[/TD]
[TD]512BW[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Sam:512BW[/TD]
[TD]Purchase - Hardware for Resale[/TD]
[TD]-1[/TD]
[TD] -[/TD]
[TD] (119.52)[/TD]
[TD] 448.22[/TD]
[TD]Sam[/TD]
[TD]Misc HW[/TD]
[TD]512BW[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]10-Jan-17[/TD]
[TD]170673[/TD]
[TD]Misc - SW:ILT-ORT[/TD]
[TD]Sales - Software[/TD]
[TD]6[/TD]
[TD] 261.50[/TD]
[TD] 1,569.00[/TD]
[TD] 2,017.22[/TD]
[TD]Misc - SW[/TD]
[TD]Misc-SW[/TD]
[TD]ILT-ORT[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]10-Jan-17[/TD]
[TD]170673[/TD]
[TD]Misc - SW:ILT-ORT[/TD]
[TD]Purchase - Software for Resale[/TD]
[TD]-6[/TD]
[TD] -[/TD]
[TD] (1,358.65)[/TD]
[TD] 658.57[/TD]
[TD]Misc - SW[/TD]
[TD]Misc-SW[/TD]
[TD]ILT-ORT[/TD]
[/TR]
</tbody>[/TABLE]
i.e. line 3 should show "Brand" as "ABC" instead of "Misc - HW"
[TABLE="width: 982"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Date[/TD]
[TD]Num[/TD]
[TD]Item[/TD]
[TD]Account[/TD]
[TD]Qty[/TD]
[TD]Sales Price[/TD]
[TD]Amount[/TD]
[TD]Balance[/TD]
[TD]Product[/TD]
[TD]Brand[/TD]
[TD]SKU[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]ABC:Chassis123[/TD]
[TD]Sales - Hardware[/TD]
[TD]1[/TD]
[TD] 1,300.00[/TD]
[TD] 1,300.00[/TD]
[TD] 1,300.00[/TD]
[TD]ABC[/TD]
[TD]ABC[/TD]
[TD]Chassis123[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]ABC:Chassis123[/TD]
[TD]Purchase - Hardware for Resale[/TD]
[TD]-1[/TD]
[TD] -[/TD]
[TD] (479.32)[/TD]
[TD] 820.68[/TD]
[TD]ABC[/TD]
[TD]ABC[/TD]
[TD]Chassis123[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Intel:I77700K[/TD]
[TD]Sales - Hardware[/TD]
[TD]1[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 820.68[/TD]
[TD]Intel[/TD]
[TD]Misc HW[/TD]
[TD]I77700K[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Intel:I77700K[/TD]
[TD]Purchase - Hardware for Resale[/TD]
[TD]-1[/TD]
[TD] -[/TD]
[TD] (124.00)[/TD]
[TD] 696.68[/TD]
[TD]Intel[/TD]
[TD]Misc HW[/TD]
[TD]I77700K[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Logitech:MU-26[/TD]
[TD]Sales - Hardware[/TD]
[TD]1[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 696.68[/TD]
[TD]Logitech[/TD]
[TD]Misc HW[/TD]
[TD]MU-26[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Logitech:MU-26[/TD]
[TD]Purchase - Hardware for Resale[/TD]
[TD]-1[/TD]
[TD] -[/TD]
[TD] (2.00)[/TD]
[TD] 694.68[/TD]
[TD]Logitech[/TD]
[TD]Misc HW[/TD]
[TD]MU-26[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Logitech:K241[/TD]
[TD]Sales - Hardware[/TD]
[TD]1[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 694.68[/TD]
[TD]Logitech[/TD]
[TD]Misc HW[/TD]
[TD]K241[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Logitech:K241[/TD]
[TD]Purchase - Hardware for Resale[/TD]
[TD]-1[/TD]
[TD] -[/TD]
[TD] (3.00)[/TD]
[TD] 691.68[/TD]
[TD]Logitech[/TD]
[TD]Misc HW[/TD]
[TD]K241[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Memory:8GB[/TD]
[TD]Sales - Hardware[/TD]
[TD]2[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 691.68[/TD]
[TD]Memory[/TD]
[TD]Misc HW[/TD]
[TD]8GB[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Memory:8GB[/TD]
[TD]Purchase - Hardware for Resale[/TD]
[TD]-2[/TD]
[TD] -[/TD]
[TD] (78.28)[/TD]
[TD] 613.40[/TD]
[TD]Memory[/TD]
[TD]Misc HW[/TD]
[TD]8GB[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Microsoft:WIN7PRO[/TD]
[TD]Sales - Software[/TD]
[TD]1[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 613.40[/TD]
[TD]Microsoft[/TD]
[TD]Misc HW[/TD]
[TD]WIN7PRO[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Microsoft:WIN7PRO[/TD]
[TD]Purchase - Software for Resale[/TD]
[TD]-1[/TD]
[TD] -[/TD]
[TD] (42.00)[/TD]
[TD] 571.40[/TD]
[TD]Microsoft[/TD]
[TD]Misc HW[/TD]
[TD]WIN7PRO[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Misc - HW:Z270[/TD]
[TD]Sales - Hardware[/TD]
[TD]1[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 571.40[/TD]
[TD]Misc - HW[/TD]
[TD]Misc HW[/TD]
[TD]Z270[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Misc - HW:Z270[/TD]
[TD]Purchase - Hardware for Resale[/TD]
[TD]-1[/TD]
[TD] -[/TD]
[TD] (62.14)[/TD]
[TD] 509.26[/TD]
[TD]Misc - HW[/TD]
[TD]Misc HW[/TD]
[TD]Z270[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]MPN:XVC00-PB[/TD]
[TD]Sales - Hardware[/TD]
[TD]1[/TD]
[TD] 130.00[/TD]
[TD] 130.00[/TD]
[TD] 639.26[/TD]
[TD]MPN[/TD]
[TD]MPN[/TD]
[TD]XVC00-PB[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]MPN:XVC00-PB[/TD]
[TD]Purchase - Hardware for Resale[/TD]
[TD]-1[/TD]
[TD] -[/TD]
[TD] (71.52)[/TD]
[TD] 567.73[/TD]
[TD]MPN[/TD]
[TD]MPN[/TD]
[TD]XVC00-PB[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Sam:512BW[/TD]
[TD]Sales - Hardware[/TD]
[TD]1[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 567.73[/TD]
[TD]Sam[/TD]
[TD]Misc HW[/TD]
[TD]512BW[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]9-Jan-17[/TD]
[TD]170671[/TD]
[TD]Sam:512BW[/TD]
[TD]Purchase - Hardware for Resale[/TD]
[TD]-1[/TD]
[TD] -[/TD]
[TD] (119.52)[/TD]
[TD] 448.22[/TD]
[TD]Sam[/TD]
[TD]Misc HW[/TD]
[TD]512BW[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]10-Jan-17[/TD]
[TD]170673[/TD]
[TD]Misc - SW:ILT-ORT[/TD]
[TD]Sales - Software[/TD]
[TD]6[/TD]
[TD] 261.50[/TD]
[TD] 1,569.00[/TD]
[TD] 2,017.22[/TD]
[TD]Misc - SW[/TD]
[TD]Misc-SW[/TD]
[TD]ILT-ORT[/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]10-Jan-17[/TD]
[TD]170673[/TD]
[TD]Misc - SW:ILT-ORT[/TD]
[TD]Purchase - Software for Resale[/TD]
[TD]-6[/TD]
[TD] -[/TD]
[TD] (1,358.65)[/TD]
[TD] 658.57[/TD]
[TD]Misc - SW[/TD]
[TD]Misc-SW[/TD]
[TD]ILT-ORT[/TD]
[/TR]
</tbody>[/TABLE]