Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@sunny_talwar Any help is appreciated 🙂
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;