Remove rows with 0 subtotal

[charu_jain]

Hello All,

I am working on a Pivot Table. I wish to remove rows where subtotal of a column name ='Summation' is equal to 0. 

Please find attached below the way data looks:

Car	Model	Model No	Model Description	Year	Month	Count	Summation
A	S	1234	Sedan	2021	Feb	10	($300)
					March	20	$300
				Totals			$0
	X	345	SUV	2021	June	4	($100)
					July	10	$200
				Totals			$100
B	P	abc	Sedan	2021	Feb	7	($3)
					March	2	$3
				Totals			$0
	Q	def	SUV	2021	June	4	($100)
					July	10	$500
				Totals			$400

For example: As can be seen from above table, I wish to remove rows with Model - S, Model No - 1234, Model Description-Sedan, Year - 2021, Month-feb, march, count -10,20 , Summation (300), 300 as the Sub Total of Summation is 0.

Request you to please help me with this.

[vinieme12]

as below

temp:
load 
if(len(Car),Car,peek(Car)) as Car
,if(len(Model),Model,peek(Model)) as Model

,if(len(ModelNo),ModelNo,peek(ModelNo)) as ModelNo
,if(len(ModelDescription),ModelDescription,peek(ModelDescription)) as ModelDescription
,if(len(Year),Year,peek(Year)) as Year
,if(len(Month),Month,'Sub') as Month
,if(len(Count),Count,'Sub') as Count
,Summation
Inline [
Car,Model,ModelNo,ModelDescription,Year,Month,Count,Summation
A,S,1234,Sedan,2021,Feb,10,($300)
 , , , , ,March,20,$300
 , , , ,Totals, , ,$0
 ,X,345,SUV,2021,June,4,($100)
 , , , , ,July,10,$200
 , , , ,Totals, , ,$100
B,P,abc,Sedan,2021,Feb,7,($3)
 , , , , ,March,2,$3
 , , , ,Totals, , ,$0
 ,Q,def,SUV,2021,June,4,($100)
 , , , , ,July,10,$500
 , , , ,Totals, , ,$400
];

inner join(temp)
load Car,Model,ModelNo,ModelDescription Resident temp Where Summation<>0 and Count='Sub';

[charu_jain]

Hello Vineeth,

Thanks for providing response. I have provided sample data above. I actually have dynamic data and there are a lot of rows in the table. It would be difficult to enter each manually.

Request you to please let me know if there is any other way to achieve this.

I was thinking if there is any way to filter subtotal that would also work. Thanks!