[SOLVED] Eliminate old records

June 22, 2010, 10:25 AM

Enigma006

Hai all

I am working on a report and I am stuck at a point after completing the whole thing. I am sure someone would help me on this.
Consider this is the table structure

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

In this sample, for an ID named "ID1" there are 5 records as shown. But I need to show only 3 records named Level 3,4 and 5 because 3 replaced 1 (as shown in pre-level) and 4 replaced 2. So its like records with level 3,4 and 5 are only active. I need to eliminate level 1 and 2. There could be any number of levels.Please guide how to achieve this.

Thanks a lot..!!

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

June 22, 2010, 11:10 AM

Dave

out of the blue...

something like this might work. ( not tested )

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

By matching the file to itself. The 'old' has BY LEVEL and the 'new' has BY PRELEVEL. After match being 'old-not-new'. Should do the trick... I think...

might need some tweaking...

Greets,Dave

June 22, 2010, 11:34 AM

Enigma006

Thanks Dave. This seemed to be very good thot. I worked on it but how ever I changed its giving me all 5 records..!!

June 22, 2010, 12:31 PM

RSquared

Try doing a define
DEFINE YOURFILE
PRINT_LEVEL/A1=IF PRELEVEL GT 0 THEN PRELEVEL
ELSE LEVEL;
END
TABLE YOURFILE
PRINT
ID LEVEL PRELEVEL
BY PRINT_LEVEL
END

June 22, 2010, 01:15 PM

Enigma006

Hai RSquared,

I don't think this logic works.

Thank you

June 22, 2010, 02:08 PM

RSquared

Enigma006

Why not? Other than it may be a numeric field.
Hae you tried it?

June 22, 2010, 02:18 PM

Enigma006

Its alpha numeric field. I did try it.
I think there should be a WHERE condition too.
I dont see use of just creating PRINT_LEVEL and sorting by it..?! How will this eliminate records?

June 22, 2010, 02:33 PM

Francis Mariani

I don't know if this is overkill, but in this example, I create a FOCUS DB and then do a recursive join:

-* 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

June 22, 2010, 02:55 PM

GamP

A somewhat simpler solution could be:

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

June 22, 2010, 03:08 PM

RSquared

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

June 22, 2010, 03:20 PM

Enigma006

Thanks RSquared. This worked. I am happy that I am going in similar direction before you posted this. thanks a lot..

June 22, 2010, 03:51 PM

njsden

quote:
Thanks RSquared. This worked.

That code would work *only* if you're always dealing with a single ID and if you never have a case of recursive level overriding. See for example this set of data (borrowed from GamP's example):

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

I would expect the following LEVELS to be displayed as a result:

Both Francis and GamP's techniques handle those cases accurately and do so even when multiple ID's exist so, unless your data contains always 1 single ID and no multiple/recursive overriding ever exists, you should attempt to implement either of those 2 techniques. RSquared's sample is a fine attempt but would not be sufficient for more complex cases.

By the way, thank you Francis for going through the trouble of working out the master file definition that allowed to have a "runnable" sample code

- Neftali.

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

June 22, 2010, 04:01 PM

Francis Mariani

I have to say I got the idea of -WRITEing Masters from Tony.

June 22, 2010, 06:25 PM

Waz

How about this one.....

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

June 22, 2010, 08:26 PM

njsden

quote:
DB_LOOKUP

Wow, there's always something new under the sky (to me at least).

When Enigma006 posted his original question the lazy guy within me was just hoping that there would be a sort of "LAST on steroids" keyword that would allow me to inspect not only the previously retrieved record but all of them at once. I guess DB_LOOKUP does precisely that!

Now it's going to be RTFM time again. Thanks Waz!

June 22, 2010, 08:34 PM

Waz

I guess RTFM is Read The Functions Manual

June 23, 2010, 08:50 AM

njsden

I was going to Read the FOCUS Manual but your version works just as fine

June 24, 2010, 05:23 AM

GamP

This DB_LOOKUP thing apparently works fine for you guys, but my server (769) does not like it - it crashes.
So I'll just stick with the two-step approach.
The 'F' in RTFM could also mean something quite different ...

June 24, 2010, 09:47 AM

njsden

Focal-Point perhaps?

June 24, 2010, 12:25 PM

Hua

I am not fully understand this DB_LOOKUP, it seems to me that any subsequent lookup is starting from the position of the previous sucessful DB_LOOKUP, not the entire file. You might have a missing returned even though the search value does exist in the file. Is this the case?

Hua

June 24, 2010, 02:13 PM

Hua

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

Respect The Female Members, of course.

June 24, 2010, 06:01 PM

Waz

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

If you check the documentation it says that for flat files, they must be in sequence.

June 25, 2010, 10:36 AM

Hua

Thank you, Waz.

Is it about RTFM? believe or not, I have read this particular section in the documentation, my brain does not want to register them most of the time...

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.

I thought DB_LOOKUP is a function, that every request with specific search values is a separate & unique request. If it works like join, I would prefer to join them, because I can access all the fields in xreference file.

Hua

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!

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!

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!

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!

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

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