topic Remove rows with 0 subtotal in App Development

Remove rows with 0 subtotal

charu_jain — Tue, 07 Jun 2022 04:08:51 GMT

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.

Re: Remove rows with 0 subtotal

vinieme12 — Wed, 08 Jun 2022 10:20:42 GMT

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';

Re: Remove rows with 0 subtotal

charu_jain — Mon, 13 Jun 2022 19:47:26 GMT

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!