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,

Hi rogerwilkouk,
That’s a nice mind bender

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?

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.

Nicely compact and to the point, David. This probably turned into our go-to example for hierarchical FOCUS queries. Awesome.

Brilliant solution David,
Thanks for sharing and reminding us of our FML option.

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.

Thanks for the reminder, and use of SET SHOWBLANKS = ON David.

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.

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.