Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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....
Hi dathu,
please ask ur question clearly .... do u mean ur child means full_path or what ?
FULL_PATH refers as a Tree and I want to load Final Node of FULL_PATH into report..
FULL_PATH is unbalanced (n-level nodes) tree and I want to load nth node FULL_PATH on the tree.
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...)
Hi,
one solution could be:
or
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
Check this qvw
vParntId=Concat(DISTINCT Chr(39) & PARENT_ID & Chr(39), ',')
Use the expression MaxString({<ID-={$(vParntId)}>} FULL_PATH)