Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     SOLVED Resequencing Data
Go
New
Search
Notify
Tools
Reply
  
SOLVED Resequencing Data
 Login/Join
 
Silver Member
posted
I have been wrestling with this for hours and I just can't seem to get my head around a way to do this.

I have the data I need in the top Format below, but I need it in the sequence in the lower Format.
The data is built from reading a file multiple times to see if there is child data (the parent and child data is all in the same file). So it reads the parent info (level 1) using the Item Number and gets all the level 1's. It then re-reads the file using the Component Item Number as the Parent to get all the Level 2's. It then re-reads the file using these Component Item Numbers as the parent to get all the Level 3's and so on (up to possibly 15 levels).
As such, the data comes out in the order it appends the data to the file which is basically in Level order.

However, they want to see it sequenced so that if the particular Level 1 has a level 2, it needs to print below it and then if that level 2 has a level 3, then print that below the level 2 and so on.
Some might have no lower level and some might have multiple lower levels but only one of those lower levels has another subsequent level and so on.

Hopefully all that makes sense.

Current Output
Level Item Number Component Item Number
1 AB11914A FA11915A
1 AB11914A PA70207
1 AB11914A PA70797
2 FA11915A AP90001A
2 FA11915A PA71143
2 FA11915A SO11074B11
3 AP90001A PA70133
3 AP90001A PA70136
3 SO11074B11 PB70540
3 SO11074B11 PB70541
3 SO11074B11 PB70656
4 PA70136 PA1
4 PB70541 PA
5 PA1 PA123456

Required Output (I added the indentations to make it easier to see what I am needing)
Level Item Number Component Item Number
1 AB11914A FA11915A
2 FA11915A AP90001A
3 AP90001A PA70133
3 AP90001A PA70136
4 PA70136 PA1
5 PA1 PA123456
2 FA11915A PA71143
2 FA11915A SO11074B11
3 SO11074B11 PB70540
3 SO11074B11 PB70541
4 PB70541 PA
3 SO11074B11 PB70656
1 AB11914A PA70207
1 AB11914A PA70797

It seems like it should be easy but for some reason I can't get my head round how to do it, either by resorting the output or by changing the way it retrieves the data initially.

Any and all help would be greatly appreciated.

This message has been edited. Last edited by: rogerwilkouk,


WF 81.5, Windows7
AS/400 Database.
All Outputs

 
Posts: 46 | Registered: November 26, 2008Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hi rogerwilkouk,
That’s a nice mind bender Good One

I’m sure some of the old-schoolers will have a more elequent mathematical algorithm to suggest.

As a 1st stab, I would compute 15 temp columns for each record pair from source to keep track of sequences for the various levels that it is associated with.
I.e. tlev1 tlev2 ... tlev15
And compute sequence value for each record pair read.
Are you familiar with using LAST to reference the previous record values?


--------------------------------------------------------------------------------
prod: WF/AS 8.2.05; OmniGen;
In FOCUS since 1991
 
Posts: 104 | Location: United Kingdom | Registered: February 07, 2008Reply With QuoteReport This Post
Master
posted Hide Post
Perhaps take a look at FML Hierarchies?

The following model ties to the input and output you show:
-*
-* Create a MASTER file on the fly.
-*
EX -LINES * EDAPUT MASTER,HIERARCHY,CV,FILE
FILENAME=HIERARCHY, SUFFIX=FOC
 SEGNAME=SEG01, $
  FIELDNAME=LEVEL, LEVEL, A1,$
  FIELDNAME=PARENT, PARENT, A12, PROPERTY=PARENT_OF, REFERENCE=CHILD,$
  FIELDNAME=CHILD, CHILD, A12,$
EDAPUT*
-RUN
-*
-* Initialize data file.
-*
CREATE FILE HIERARCHY
-*
-* Load component data.
-*
MODIFY FILE HIERARCHY
DATA
'1','AB11914A',    'FA11915A',$
'1','AB11914A',    'PA70207',$
'1','AB11914A',    'PA70797',$
'2','FA11915A',    'AP90001A',$
'2','FA11915A',    'PA71143',$
'2','FA11915A',    'SO11074B11',$
'3','AP90001A',    'PA70133',$
'3','AP90001A',    'PA70136',$
'3','SO11074B11',  'PB70540',$
'3','SO11074B11',  'PB70541',$
'3','SO11074B11',  'PB70656',$
'4','PA70136',     'PA1',$
'4','PB70541',     'PA',$
'5','PA1',         'PA123456',$
END
-RUN
-*
-* Create Hierarchy Report
-*
SET SHOWBLANKS = ON
TABLE FILE HIERARCHY
PRINT PARENT LEVEL
FOR CHILD
AB11914A GET CHILDREN ALL
ON TABLE HOLD AS HIERREPT 
END
-RUN
-*
-* Create Component Report for user.  
-*
SET PAGE = OFF
TABLE FILE HIERREPT
"Component Report"
PRINT LEVEL  AS 'Level'
      PARENT AS 'Item'
      E01    AS 'Component'
ON TABLE SET STYLE *
 INCLUDE=jellybean_combo.sty, $
TYPE = TITLE, JUSTIFY=CENTER,$
ENDSTYLE
END   


This model uses EDAPUT to create a temporary MASTER for demo purposes. In a real system, you would probably want to create a permanent master.
 
Posts: 758 | Registered: April 23, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Nicely compact and to the point, David. This probably turned into our go-to example for hierarchical FOCUS queries. Awesome.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1639 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Brilliant solution David,
Thanks for sharing and reminding us of our FML option.
Nice Thread


--------------------------------------------------------------------------------
prod: WF/AS 8.2.05; OmniGen;
In FOCUS since 1991
 
Posts: 104 | Location: United Kingdom | Registered: February 07, 2008Reply With QuoteReport This Post
Silver Member
posted Hide Post
David,
I really like the look of this. I am going to have to do some serious research to get this working.
Thank you so much.


WF 81.5, Windows7
AS/400 Database.
All Outputs

 
Posts: 46 | Registered: November 26, 2008Reply With QuoteReport This Post
Expert
posted Hide Post
Thanks for the reminder, and use of SET SHOWBLANKS = ON David.
 
Posts: 2967 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Silver Member
posted Hide Post
David.
This worked Awesome. You've saved me a lot of hair pulling.
Even though I've got it working I am going to do a lot more reading on this to ensure I fully understand it.


WF 81.5, Windows7
AS/400 Database.
All Outputs

 
Posts: 46 | Registered: November 26, 2008Reply With QuoteReport This Post
Master
posted Hide Post
Thanks all!

Glad you got it working Roger.

Yes, even though it is a small model, there are something interesting things going on:
* building a master for a FOCUS file.
* loading a FOCUS file.
* using FML.

Yes, for sure read up on these components, as they can be used as building blocks to solve other types of problems, as well.
 
Posts: 758 | Registered: April 23, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     SOLVED Resequencing Data

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.