Need to create a column that capture the data flow in my table

[Shriram_Sridhar]

Hi,

I have a table like this below; 

Data Type	Source	Target
DT1	A	B
DT1	B	C
DT1	C	D
DT1	D	C
DT1	C	B
DT1	E	F
DT2	A	B

I need to create a column which captures the date flow between source and the target based on my data type. I'd like to create a new column (Data flow) that looks like below; 

Data Type	Source	Target	Data Flow
DT1	A	B	A
DT1	B	C	A to B
DT1	C	D	A to B to C
DT1	D	C	A to B to C to D
DT1	C	B	A to B to C to D to C
DT1	E	F	E to F
DT2	A	B	A to B

I have tried hierarchy, but it didn't go well as I have circular kind of loop in my table (A to B to C to D to C to B). Can you kindly help me?

[Kushal_Chawda]

@Shriram_Sridhar  Your output is bit unclear. For eg. DT1 C & B, how it is A to B to C to D to C? Should it be not A to B to C to B?

[Shriram_Sridhar]

Hi Kushal,

The Data once reaches D, it flows backward from D to C to B and stops. If the flow is like a linked list say, A to B to C to D, it is achievable. In my case, I have some data flowing from Target to the source again. I hope this clears your question 🙂 

[Prashant_Naik20]

Hi Sridhar,

I tried something hope this will help you

Script - 
t1:
load * inline [
DataType,Source,Target,sort
DT1,A,B,1
DT1,B,C,2
DT1,C,D,3
DT1,D,C,4
DT1,C,B,5
DT1,E,F,6
DT2,A,B,7
];

NoConcatenate
tmp:
Load *,
if(Previous(Target)=Source,Peek(Previous_Target)&' to '&Source,Source) as Previous_Target
Resident t1;

drop Table t1;

NoConcatenate
tmp2:
load *,
if(RowNo()=1,Source,if(Previous(Target)<>Source,Source&' to '&Target,Previous_Target)) as New_field
Resident tmp;

drop table tmp;

[Qrishna]

i believe this is a duplicate of https://community.qlik.com/t5/New-to-Qlik-Analytics/How-to-Create-a-Dynamic-Path-Column-Based-on-Sou....

Check the code from the above link.