MATCH FILE YOURFILE
PRINT ID LEVEL PRELEVEL
BY LEVEL
RUN
FILE YOURFILE
PRINT ID LEVEL PRELEVEL
BY PRELEVEL
AFTER MATCH HOLD OLD-NOT-NEW
END

-* enigma1.fex

-SET &ECHO='ALL';

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
-RUN

FILEDEF FILE1 DISK file1.txt
-RUN

-WRITE FILE1 ID1 1
-WRITE FILE1 ID1 2
-WRITE FILE1 ID1 3 1
-WRITE FILE1 ID1 4 2
-WRITE FILE1 ID1 5

FILEDEF MASTER DISK file1.mas
-RUN

-WRITE MASTER FILENAME=FILE1, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=FILE1, $
-WRITE MASTER FIELDNAME=ID      , FORMAT=A3, ACTUAL=A3, $
-WRITE MASTER FIELDNAME=FILL1   , FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=LEVEL   , FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=FILL2   , FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=PRELEVEL, FORMAT=A1, ACTUAL=A1, $
-RUN

DEFINE FILE FILE1
LEVEL1/A4    = ID | LEVEL;
PRELEVEL1/A4 = ID | PRELEVEL;
END
-RUN

TABLE FILE FILE1
SUM
MIN.ID
MIN.LEVEL
MIN.PRELEVEL
BY LEVEL1
BY PRELEVEL1
ON TABLE HOLD AS FILE1H FORMAT FOCUS INDEX LEVEL1 PRELEVEL1
END
-RUN

JOIN LEVEL1 IN FILE1H TO PRELEVEL1 IN FILE1H AS J1
-RUN

?FF FILE1H
-RUN

TABLE FILE FILE1H
PRINT 
ID
LEVEL
PRELEVEL

WHERE J1LEVEL1 EQ ''
END
-RUN

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

FILEDEF FILE1 DISK file1.txt
-RUN

-WRITE FILE1 ID1 1
-WRITE FILE1 ID1 2
-WRITE FILE1 ID1 3 1
-WRITE FILE1 ID1 4 2
-WRITE FILE1 ID1 5
-WRITE FILE1 ID1 6
-WRITE FILE1 ID1 7 5
-WRITE FILE1 ID1 8
-WRITE FILE1 ID1 9
-WRITE FILE1 ID2 1
-WRITE FILE1 ID2 2
-WRITE FILE1 ID2 3 1
-WRITE FILE1 ID3 1 
-WRITE FILE1 ID3 2 1
-WRITE FILE1 ID3 3
-WRITE FILE1 ID3 4 3
-WRITE FILE1 ID3 5
-WRITE FILE1 ID3 6

FILEDEF MASTER DISK file1.mas
-RUN

-WRITE MASTER FILENAME=FILE1, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=FILE1, $
-WRITE MASTER FIELDNAME=ID      , FORMAT=A3, ACTUAL=A3, $
-WRITE MASTER FIELDNAME=FILL1   , FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=LEVEL   , FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=FILL2   , FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=PRELEVEL, FORMAT=A1, ACTUAL=A1, $
-RUN

TABLE FILE FILE1
PRINT ID PRELEVEL
IF PRELEVEL NE ' '
ON TABLE SAVE
END

DEFINE FILE FILE1
IDLEV/A4 = ID | LEVEL;
END

TABLE FILE FILE1
PRINT *
IF IDLEV NE (SAVE)
END

GamP

---

- Using AS 8.2.01 on Windows 10 - IE11.
	in Focus since 1988

quote:

ID Level Pre-level
ID1 1
2
3 1
4 2
5

  

DEFINE YOURFILE
PRINT_LEVEL/A1=IF PRELEVEL GT 0 THEN PRELEVEL
ELSE LEVEL;
END
This will give you the following
ID Level Pre-level Print_Level
       ID1 1            1
           2            2
           3     1      1
           4     2      2
           5            5

TABLE YOURFILE
PRINT
ID PRELEVEL
BY PRINT_LEVEL
BY HIGHEST LEVL
ON TABLE HOLD AS HOLDIT
END
This will give you the following
       ID Level Pre-level Print_Level
       ID1 3     1      1
           1            1
           4     2      2
           2            2
           5            5

DEFINE FILE HOLDIT
BYPASS_SW/A=IF Print_Level EQ LAST Print_Level
            THEN 'Y' ELSE 'N';
END
TABLE FILE HOLDIT
PRINT *
WHERE BYPASS_SW EQ 'N';
END

quote:

Thanks RSquared. This worked.

ID Level Pre-level
ID1  1
     2
     3 1   <-- 3 overrides 1
     4 2
     5
     6 3   <-- but 6 overrides 3
     7 5
     8
     9

4
6
7
8
9

FILEDEF FILE1 DISK file1.txt
-RUN

-WRITE FILE1 ID1 1
-WRITE FILE1 ID1 2
-WRITE FILE1 ID1 3 1
-WRITE FILE1 ID1 4 2
-WRITE FILE1 ID1 5

FILEDEF MASTER DISK file1.mas
-RUN

-WRITE MASTER FILENAME=FILE1, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=FILE1, $
-WRITE MASTER FIELDNAME=ID      , FORMAT=A3, ACTUAL=A3, $
-WRITE MASTER FIELDNAME=FILL1   , FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=LEVEL   , FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=FILL2   , FORMAT=A1, ACTUAL=A1, MISSING=ON, $
-WRITE MASTER FIELDNAME=PRELEVEL, FORMAT=A1, ACTUAL=A1, $
-RUN
TABLE FILE FILE1
PRINT *
      COMPUTE
      PREV_USED/A1 MISSING ON = DB_LOOKUP(FILE1,LEVEL,PRELEVEL,FILL2);
WHERE TOTAL PREV_USED IS MISSING
END

Waz...

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

quote:

DB_LOOKUP

quote:

The 'F' in RTFM could also mean something quite different ...

quote:

it seems to me that any subsequent lookup is starting from the position of the previous

Waz...

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

quote:

There are no restrictions on the source file. The lookup file can be any non-FOCUS data source that is supported as the cross referenced file in a cluster join. The lookup fields used to find the matching record are subject to the rules regarding cross-referenced join fields for the lookup data source. A fixed format sequential file can be the lookup file if it is sorted in the same order as the source file.