Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have a table that is having the train information like train no , train name, source station name, destination station name, distance
the table looks like this:
Train No Train Name Source Station Name Destination Name Distance
--------------------------------------------------------------------------------------------------------------
1 ABC A B 10
2 CDE B E 30
3 EFG E D 40
4 HIJ E F 20
5 KLM B D 10
what I need is if I select Source Station : A and Destination : D then it should give the me list of all the train numbers, I need to connect to reach to the destination using shortest distance .
For example: from A To D : A->B->D = 20 then the train no will be 1,5
2: A->B->E->D= 80 then the train numbers will be 1,2,3
This is what I need , it would be great if anyone can help me finding the solution
Thanks,
Vivek
Since this calculation involves a recursive calc I don´t think this can be done using regular QlikView expressions. Maybe using a macro (not sure about this)
Maybe you can create a solution using a script that creates hierarchy tables.
Hi Swuehl,
Can you give me the expressions that you have used , so that i will try
Thank you,
Vivek
Attached is the script code.
I then created the two list boxes for SourceSelect and DestinationSelect and a straight table:
=Aggr(If(Left(Path,1) = SourceSelect and Right(Path,1) = DestinationSelect, Path),Path) | sum({<AncestorName = {"*"}>}Distance) | Concat(AncestorName,',') |
---|---|---|
A/B/D | 20 | 1,5 |
A/B/E/D | 80 | 1,2,3 |
That's just an outline of a possible solution (not even sure if this will work for more complex scenarios).
Hi Swuehl,
In the script u have loaded concatenated the following
CONCATENATE
LOAD Null() as [Source Station Name],[Source Station Name] as [Destination Name]
RESIDENT Trains;
Why do we need to do that, if not what will happen can you explain me
Thanks,
Vivek
In a hierarchy LOAD, the root nodes should be loaded with parent node being NULL.
Not sure if we need this here, if you want to know what happens if you remove this part, just try it.
edit: I think if you remove the concatenated records, the HIERARCHY LOAD prefix will not work correctly.
From the HELP:
All nodes with a parent id not found in the node id column (including nodes with missing parent id) will be considered as roots. Also, only nodes with a connection to a root node - direct or indirect - will be loaded, thus avoiding circular references.
Hi Swuehl,
If a person want to go from B to A , if he selects B in source and A in destination is it possible and if I have a record which shows the information of the train that travels from B to A. In that case what should I do
Thanks
Vivek
It should be possible if your source data actually contains possible routes for that combination of Source and Destination. Currently, in your sample records, it doesn't, right?
Hi Swuehl,
I tried it by inserting a record but it does not works, should I need to change any thing else.
Please let me know
Thank You,
Vivek