Compute Start and End from Route along with Value

[sbfernandes]

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?

[Gysbert_Wassenaar]

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

[sasiparupudi1 · Accepted solution]

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;

[sbfernandes]

Gysbert, thanks for this. Was unware of this.

[sbfernandes]

Sasidhar, thank you for this solution.