Focal Point
Storing hierarchies
January 14, 2008, 11:55 PM
KaranthStoring hierarchies
Hi,
I would like to store a hierarchy as
Source is
ID parent id
1
2 1
3 2
4 3
i want to have a data store as
id hierarchy
1 NULL
2 1 2
3 1 2 3
4 1 2 3 4
Any way to do this?
Thanks
January 15, 2008, 02:25 AM
Tony AKaranth,
Just use recursive joins if you have a finite structure. Index the parentid on your table and use
something like this -
JOIN CLEAR *
JOIN LEFT_OUTER id IN table TAG T1 to MULTIPLE parent IN table TAG T2 AS J1
JOIN LEFT_OUTER T1.id IN table to MULTIPLE parent IN table TAG T3 AS J2
JOIN LEFT_OUTER T2.id IN table to MULTIPLE parent IN table TAG T4 AS J3
TABLE FILE table
BY T1.id
BY T2.id
BY T3.id
BY T4.id
END
T
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | | |
WebFOCUS App Studio 8.2.06 standalone on Windows 10 | |
October 09, 2009, 08:09 AM
Enigma006Hey TOny
What if I dont need the parent at the end.
I guess it should be like
1
2 1
3 1 2
4 1 2 3
I am working on the same.
Thanks
8.1.05
HTML,PDF,EXL2K, Active, All
October 09, 2009, 04:08 PM
Enigma006tony,
the code above does not work for me.
Can you change the code if my table name is t1 and parent field is pid
It gives me error in anyway i change the code.
8.1.05
HTML,PDF,EXL2K, Active, All
October 09, 2009, 04:54 PM
Tony ASo given your sample data on your other post of -
Parent Child
A B
C D
B E
F E
A F
you need to get
Parent Parent Child
A B E
C D
A F E
Am I correct? If so then you only need two lines of join code but you will need to use LEFT_OUTER. Make sure you understand the TAG syntax and how referencing it within the TABLE request gives you the fields that you want. It is so similar to SQL statements.
And from you other post, check out the search suggestion I gave you, it will help you all that you need.
T
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | | |
WebFOCUS App Studio 8.2.06 standalone on Windows 10 | |
October 09, 2009, 05:06 PM
Enigma006Thanks Tony.. I will look into TAG and will work on it.
8.1.05
HTML,PDF,EXL2K, Active, All
October 12, 2009, 11:50 AM
Enigma006Hai Tony,
I am able to get it fine but 1 problem.
As in the above example, I amgetting B and F in the 1st PARENT columns. I should not get them as they are child of other parents.
Can you help me with this.
Thanks
8.1.05
HTML,PDF,EXL2K, Active, All
October 12, 2009, 01:17 PM
SIMYou can also use Drill down reports.....Also if you know OLAP report then it is the easiest way of alll........hope this will help you...............
WEBFOCUS 5.2.5
HP-UX(UNIX)
PDF,HTML,OLAP,DRILL DOWN
October 12, 2009, 02:56 PM
Enigma006There are going to be multiple levels of hierarchy, upto 8 levels. What could be the best way? Can anyone suggest me on this.
8.1.05
HTML,PDF,EXL2K, Active, All
October 12, 2009, 03:17 PM
GinnyJakesIf you have 8 levels, you need 7 joins. All the information you need to proceed is in this post.
Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
October 12, 2009, 03:37 PM
Enigma006Hey Ginny,
Thats true 7 joins need to be there.
I need to start with nodes which have no parents but just children.some nodes have children and parents, these should not be shown as parents.
But I get these. I am not sure how to eliminate these.
7566 7566 7788 7788 7876
7902 7876
7902 7788 7369
7902 7369
7839 7566 7566 7788
7902
7698 7788
7902
7782 7788
7902
In the above 7566 is both a parent n child, I need it just as a child but not as parent.
Thanks
8.1.05
HTML,PDF,EXL2K, Active, All
October 12, 2009, 04:14 PM
Tony AHow about pulling all those without parents from your data to later join
from?
T
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | | |
WebFOCUS App Studio 8.2.06 standalone on Windows 10 | |
October 12, 2009, 04:24 PM
Enigma006I am sorry..i could not get it..working on it..
Can you show sample join about what you are suggesting Tony..
Thanks
8.1.05
HTML,PDF,EXL2K, Active, All
October 12, 2009, 04:35 PM
Enigma006this is sample code I am using Tony..
JOIN
LEFT_OUTER CHILD IN TABLE_NAME TAG G1 TO
PARENT IN TABLE_NAME TAG G2 AS J20
END
JOIN
LEFT_OUTER G2.CHILD IN TABLE_NAME TO
PARENT IN TABLE TAG G3 AS J21
END
TABLE FILE TABLE_NAME
PRINT
G3.CHILD
BY G1.PARENT AS 'Parent'
BY G1.CHILD AS 'Child'
BY G2.CHILD
END
8.1.05
HTML,PDF,EXL2K, Active, All
October 12, 2009, 04:51 PM
Tony AYou can use the following code to show you one method. The first part just creates some sample data for use within the example, everything else should be self explanitory? -
EX -LINES 4 EDAPUT MASTER,HIERARCHY,CF,MEM,FILENAME=HIERARCHY, SUFFIX=FOC,$
SEGNAME=ONE, SEGTYPE=S0 ,$
FIELD=PARENT, ,A4 ,A4 ,FIELDTYPE=I, $
FIELD=CHILD, ,A4 ,A4 , $
-RUN
CREATE FILE HIERARCHY
MODIFY FILE HIERARCHY
FREEFORM PARENT CHILD
MATCH PARENT
ON MATCH REJECT
ON NOMATCH INCLUDE
DATA
A,B,$
C,D,$
B,E,$
F,E,$
A,F,$
END
-*
TABLE FILE HIERARCHY
BY CHILD
ON TABLE HOLD AS CHILDREN
END
-*
TABLE FILE HIERARCHY
BY PARENT
IF PARENT NE (CHILDREN)
ON TABLE HOLD AS ONLY_PARENTS
END
-*
JOIN CLEAR *
JOIN PARENT IN ONLY_PARENTS TAG T1 TO MULTIPLE PARENT IN HIERARCHY TAG T2 AS J1
JOIN LEFT_OUTER T2.CHILD IN ONLY_PARENTS TO PARENT IN HIERARCHY TAG T3 AS J2
-*
TABLE FILE ONLY_PARENTS
PRINT T2.CHILD
T3.CHILD
BY T1.PARENT <-- Not really required as the host table is designed to give Only Parents (obvious really)
BY T2.PARENT
END
T
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | | |
WebFOCUS App Studio 8.2.06 standalone on Windows 10 | |
October 12, 2009, 05:01 PM
Enigma006This is the way I worked before but it did not give me results I dont know why..strange..!!
8.1.05
HTML,PDF,EXL2K, Active, All
October 12, 2009, 05:05 PM
Tony ADon't mention it, you're welcome
T
October 12, 2009, 05:09 PM
Enigma006Instead of using in file hold I used join..
that worked but I get that are both pareants and child..:-( Its like I need negation of a join..lol
8.1.05
HTML,PDF,EXL2K, Active, All
October 13, 2009, 09:10 AM
Enigma006Does anyone have suggestions please?
8.1.05
HTML,PDF,EXL2K, Active, All
October 13, 2009, 10:09 AM
Enigma006Hey Tony,
As I said in the above code, if I want to use G3.CHILD in the join, how do I use it. It gives me an error when I hold the file as focus and index it. I used ASNAMES but no use. Can you suggest something
Thanks
8.1.05
HTML,PDF,EXL2K, Active, All