How to Create a Dynamic Path Column Based on Source-Target Flow in Qlik?

[Shriram_Sridhar]

Hello Qlik Community,

I have a dataset with four columns: Source_code, Source, target_code, target, and data_type. I want to dynamically generate a new column called Path that represents the flow from the Source to the Target, traversing through all possible connections. The Path should concatenate the sources and targets in the correct sequence. Below is an example of my data and the expected output:

Data:

Source_code	Source	target_code	Target	data_type
100	A	101	B	Application
101	B	102	C	Application
102	C	103	D	Application
103	D	104	E	Application
104	E	102	C	Application
102	C	105	F	Application
1000	A	1001	B	Software

Expected Output:

Source_code	Source	target_code	Target	data_type	Path
100	A	101	B	Application	A to B
101	B	102	C	Application	A to B to C
102	C	103	D	Application	A to B to C to D
103	D	104	E	Application	A to B to C to D to E
104	E	102	C	Application	A to B to C to D to E to C
102	C	105	F	Application	A to B to C to D to E to C to F
1000	A	1001	B	Software	A to B

I would like to know how to implement this logic in Qlik Sense using scripting or a formula that builds the dynamic path for each row. Any guidance on creating this column or using Qlik’s functions would be much appreciated!

Thanks in advance for your help!

[Kushal_Chawda]

@Shriram_Sridhar  You could do it using peek() and previous() functions , but important thing is to sort your data the way it is represented. I don't see any columns which we can use to sort the data in the way it is represented. Is there any other column to sort the data correctly, probably sequence?

[Qrishna]

Try Below:

SourceData:
LOAD
RecNo() AS Sno,
Source_code,
Source,
target_code,
Target,
data_type
FROM
[https://community.qlik.com/t5/New-to-Qlik-Analytics/How-to-Create-a-Dynamic-Path-Column-Based-on-Sou...]
(html, codepage is 1252, embedded labels, table is @1);

Paths:
load *
, if(not isnull(Previous(Target)) and Source = Previous(Target), Peek(Full_Path) & ' to ' & Target, Path) as Full_Path
;
LOAD
Sno,
Source_code,
Source,
target_code,
Target,
data_type,
Source & ' to ' & Target AS Path
RESIDENT SourceData
order by Sno;

DROP TABLEs SourceData;

exit script;

you could include as many conditions as you want in

if(Source = Previous(Target) and data_type= Previous(data_type)...)

make sure, in your data: Source = Previous(Target), else this wont work and you got to use a different recursive approach.

[Shriram_Sridhar]

Many thanks, Qrishna for the solution! It is partially working, is there way to get this result without sorting. i.e, in my case, some flows are jumbled, they are not exactly one below the other. 

I have tweaked the above data set to resemble my scenario,

source_code	source	target_code	target	data_type
100	A	101	B	Application
100	A	106	G	Application
102	C	103	D	Application
101	B	102	C	Application
103	D	104	E	Application
104	E	102	C	Application
102	C	105	F	Application
1000	A	1001	B	Software

I have added A to G as an additional line (Highlighted in red), 101 comes after 102 (highlighted in blue). This is how the table looks when I run with your code.

Please note that the source and target codes are also cannot be sorted. The above is just a sample, in my data the source codes are 5 or 6 digits, A source with 6 digits comes before 5, meaning we cannot sort them based on that.

Again, many thanks for the previous solution. Your effort means a lot for us 🙂 

[Shriram_Sridhar]

Unfortunately no,  Kushal. That is the biggest challenge we are facing with this data set.

[Qrishna]

i've already mentioned that the above code works only when Source = Previous(Target).Thats ok even if you have 5 or 6 digits ids, we really dont need sort until and unless we have some kind of relation between a target and its peer. when you want a sequence between A to G i.e A to B to C to D to E to F to G, you have to have relation of some kind between A and B .. F and G, but i dont see that in your sample data. there is break in relation between E to F and F to G.

but you may have something like below for the 2nd table provided:

pls provide some 5-10 rows of your dataset so i can see how the source and target field values are behaving and the relation among each other.

[Kushal_Chawda]

@Shriram_Sridhar  That's the point here. It's not possible that you don't have any sequence, otherwise how do you know that A to B is the first line of the source to target in application data_type? If you don't have that probably you could ask this question to data owner. Besides it's not hierarchical data, so you can't really use hierarchical function here. So you may need to find the sequence of your data so that you can sort it.