Calculate Grid sum aggregated by same Start ID and End ID

[markgraham123]

Hi All,

I'm struck on a grid aggregation issue. Please help.

I'm trying to create an aggregated sum of values for 'Start ID - Ending ID' category .

Start ID	Ending ID	Ending ID	Ending ID	Ending ID	Ending ID	Ending ID
	3	5	6	7	8	9
1	2	-	-	1	-	-
2	2	1	-	1	-	-
3	7	3	-	-	1	1
4	-	-	-	-	1	1
5	-	5	3	3	1	3
6	-	-	-	1	-	2
7	-	-	-	1	2	-
8	-	-	-	-	6	1
9	-	-	-	-	-	9

StartID and Ending ID should be the same while calculating sum of grid values. Any help is highly appreciated. 

E.g. Output:  

3*3 will get value of 11

5*5 - 20

6*6 - 23

7*7 - 30

8*8 - 41

9*9 - 58

[markgraham123]

@sunny_talwar  Any help is appreciated 🙂

[Kushal_Chawda]

T1:
CrossTable(EndID,Value)
LOAD
if(len(trim("Start ID"))=0,0,"Start ID") as "Start ID",
"Ending ID",
"Ending ID1",
"Ending ID2",
"Ending ID3",
"Ending ID4",
"Ending ID5"
FROM [lib://Qlik web]
(html, utf8, embedded labels, table is @1);

T2:
Load *,
if("Start ID"=0,Value,SumValue) as FinalValue;
Load *,
if("Start ID"<>0, RangeSum(Value,Peek(SumValue)),Peek(SumValue)) as SumValue
Resident T1
Order by EndID,"Start ID";

Drop Table T1;

T3:
Generic Load "Start ID",EndID,FinalValue
Resident T2;

Drop Table T2;