Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Greetings.
I have below table consisting of intermediate routes (Start-End).
Intermediate Routes | Value |
A-B | 1 |
B-C | 2 |
A-D | 3 |
D-C | 4 |
Based on the above, i would like to create a table with "Final Route" based on the start and end point and subsequent sum of value.
Output:
Final Route | Value |
---|---|
A-B-C | 3 |
A-D-C | 7 |
Is there a way to do this in Qlikview?
May be like attached
T1:
Load
*,
If(RowNo()=1,1,if(Subfield(IntermediateRoutes,'-',1)=SubField(Previous(IntermediateRoutes),'-',2),Peek(Key),Peek(Key)+1)) as Key
Inline
[
IntermediateRoutes,Value
A-B,1
B-C,2
A-D,3
D-C,4
];
T2:
Load
Key,
Concat(IntermediateRoutes,'-') as TotalIntermediateRoutes,
Sum(Value) As TotalValue
Resident T1
Group By
Key
;
T3:
NoConcatenate
Load
Key,
SubField(TotalIntermediateRoutes,'-',1)&'-'&SubField(TotalIntermediateRoutes,'-',2)&'-'&SubField(TotalIntermediateRoutes,'-',-1) AS TotalIntermediateRoutes,
TotalValue
Resident T2
;
Drop Table T2,T1;
You can treat that data like it's a hierarchy. And that means you can use the Hierarchy function to flatten that hierachy to get the complete paths and when you've got that you can add up the values to get the result. For more information about the Hierarchy function have a look at this blog post: Unbalanced, n-level hierarchies
May be like attached
T1:
Load
*,
If(RowNo()=1,1,if(Subfield(IntermediateRoutes,'-',1)=SubField(Previous(IntermediateRoutes),'-',2),Peek(Key),Peek(Key)+1)) as Key
Inline
[
IntermediateRoutes,Value
A-B,1
B-C,2
A-D,3
D-C,4
];
T2:
Load
Key,
Concat(IntermediateRoutes,'-') as TotalIntermediateRoutes,
Sum(Value) As TotalValue
Resident T1
Group By
Key
;
T3:
NoConcatenate
Load
Key,
SubField(TotalIntermediateRoutes,'-',1)&'-'&SubField(TotalIntermediateRoutes,'-',2)&'-'&SubField(TotalIntermediateRoutes,'-',-1) AS TotalIntermediateRoutes,
TotalValue
Resident T2
;
Drop Table T2,T1;
Gysbert, thanks for this. Was unware of this.
Sasidhar, thank you for this solution.