topic Re: Compute Start and End from Route along with Value in QlikView

Compute Start and End from Route along with Value

sbfernandes — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Compute Start and End from Route along with Value

Gysbert_Wassenaar — Wed, 02 May 2018 17:13:47 GMT

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

Re: Compute Start and End from Route along with Value

sasiparupudi1 — Thu, 03 May 2018 08:59:17 GMT

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;

Re: Compute Start and End from Route along with Value

sbfernandes — Thu, 03 May 2018 09:02:31 GMT

Gysbert, thanks for this. Was unware of this.

Re: Compute Start and End from Route along with Value

sbfernandes — Thu, 03 May 2018 09:03:10 GMT

Sasidhar, thank you for this solution.