topic Re: Find the Final Node on Hierarchy in QlikView

Find the Final Node on Hierarchy

Sat, 18 Oct 2014 16:41:27 GMT

Hello, I have table having ID, PARENT_ID , FULL_PATH and please find the sample data below.

ID	PARENT_ID	FULL_PATH
1		Application
2	1	Application\AAR2 Test 8-11-2014
3	1	Application\AAR3 Test 8-11-2014
4	3	Application\AAR3 Test 8-11-2014\AAR3 Asset 1
5	3	Application\AAR3 Test 8-11-2014\Asset 2 8-11-2014
6	3	Application\AAR3 Test 8-11-2014\Test 8-27-2014
7	1	Application\Corporate Security
8	7	Application\Corporate Security\Cyber Security
9	8	Application\Corporate Security\Cyber Security\Nexpose
10	9	Application\Corporate Security\Cyber Security\Nexpose\Admin
11	9	Application\Corporate Security\Cyber Security\Nexpose\Admins
12	9	Application\Corporate Security\Cyber Security\Nexpose\Web Interface
13	8	Application\Corporate Security\Cyber Security\Servers
14	13	Application\Corporate Security\Cyber Security\Servers\Vulnerability Scanner
15	14	Application\Corporate Security\Cyber Security\Servers\Vulnerability Scanner\Admins
16	14	Application\Corporate Security\Cyber Security\Servers\Vulnerability Scanner\Users
17	7	Application\Corporate Security\Pro-Watch
18	17	Application\Corporate Security\Pro-Watch\Admin
19	17	Application\Corporate Security\Pro-Watch\Badging
20	17	Application\Corporate Security\Pro-Watch\Reporting
21	17	Application\Corporate Security\Pro-Watch\User

I want load final child on the above hierarchy table. Please find the sample out put.

ID	PARENT_ID	FULL_PATH
2	1	Application\AAR2 Test 8-11-2014
4	3	Application\AAR3 Test 8-11-2014\AAR3 Asset 1
5	3	Application\AAR3 Test 8-11-2014\Asset 2 8-11-2014
6	3	Application\AAR3 Test 8-11-2014\Test 8-27-2014
10	9	Application\Corporate Security\Cyber Security\Nexpose\Admin
11	9	Application\Corporate Security\Cyber Security\Nexpose\Admins
12	9	Application\Corporate Security\Cyber Security\Nexpose\Web Interface
15	14	Application\Corporate Security\Cyber Security\Servers\Vulnerability Scanner\Admins
16	14	Application\Corporate Security\Cyber Security\Servers\Vulnerability Scanner\Users
18	17	Application\Corporate Security\Pro-Watch\Admin
19	17	Application\Corporate Security\Pro-Watch\Badging
20	17	Application\Corporate Security\Pro-Watch\Reporting
21	17	Application\Corporate Security\Pro-Watch\User

Please help me how we can do....

Re: Find the Final Node on Hierarchy

Sat, 18 Oct 2014 16:54:06 GMT

Hi dathu,

please ask ur question clearly .... do u mean ur child means full_path or what ?

Re: Find the Final Node on Hierarchy

Sat, 18 Oct 2014 16:59:05 GMT

FULL_PATH refers as a Tree and I want to load Final Node of FULL_PATH into report..

Re: Find the Final Node on Hierarchy

Sat, 18 Oct 2014 17:01:29 GMT

FULL_PATH is unbalanced (n-level nodes) tree and I want to load nth node FULL_PATH on the tree.

Re: Find the Final Node on Hierarchy

kuba_michalik — Sat, 18 Oct 2014 20:39:46 GMT

Load first table, then do a resident load on it using the following condition:

Where not Exists(PARENT_ID, ID)

(remember to add NoConcatenate before this load or it will auto-concatenate)

Then drop the original table.

This will load only the nodes which have no descendants, so they must be the final nodes (leaves, or whatever the proper terminology is...)

Re: Find the Final Node on Hierarchy

MarcoWedel — Sat, 18 Oct 2014 21:58:23 GMT

Hi,

one solution could be:

tabApplications:
Hierarchy (ID, PARENT_ID, NodeName,, NodeName, PathName, '\', Depth)
LOAD ID,
    PARENT_ID,
    FULL_PATH,
    SubField(FULL_PATH,'\',-1) as NodeName
FROM [http://community.qlik.com/thread/138536] (html, codepage is 1252, embedded labels, table is @1);

Left Join
LOAD ID,
    If(FieldIndex('PARENT_ID', ID),Dual('No',0),Dual('Yes',-1)) as IsTerminalNode
Resident tabApplications;

hope this helps

regards

Marco

Re: Find the Final Node on Hierarchy

anbu1984 — Sun, 19 Oct 2014 06:08:58 GMT

Check this qvw

vParntId=Concat(DISTINCT Chr(39) & PARENT_ID & Chr(39), ',')

Use the expression MaxString({<ID-={$(vParntId)}>} FULL_PATH)