Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Eliminate old records

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Eliminate old records
 Login/Join
 
Master
posted
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,


8.1.05
HTML,PDF,EXL2K, Active, All
 
Posts: 484 | Registered: February 03, 2009Report This Post
Master
posted Hide Post
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


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Master
posted Hide Post
Thanks Dave. This seemed to be very good thot. I worked on it but how ever I changed its giving me all 5 records..!!


8.1.05
HTML,PDF,EXL2K, Active, All
 
Posts: 484 | Registered: February 03, 2009Report This Post
Guru
posted Hide Post
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


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Master
posted Hide Post
Hai RSquared,

I don't think this logic works.

Thank you


8.1.05
HTML,PDF,EXL2K, Active, All
 
Posts: 484 | Registered: February 03, 2009Report This Post
Guru
posted Hide Post
Enigma006

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


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Master
posted Hide Post
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?


8.1.05
HTML,PDF,EXL2K, Active, All
 
Posts: 484 | Registered: February 03, 2009Report This Post
Expert
posted Hide Post
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


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
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


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Guru
posted Hide Post
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


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Master
posted Hide Post
Thanks RSquared. This worked. I am happy that I am going in similar direction before you posted this. thanks a lot..


8.1.05
HTML,PDF,EXL2K, Active, All
 
Posts: 484 | Registered: February 03, 2009Report This Post
Virtuoso
posted Hide Post
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:

4
6
7
8
9


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 Wink

- Neftali.

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



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Expert
posted Hide Post
I have to say I got the idea of -WRITEing Masters from Tony.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
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


Waz...

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

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
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!
Good One



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Expert
posted Hide Post
I guess RTFM is Read The Functions Manual
Smiler


Waz...

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

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
I was going to Read the FOCUS Manual but your version works just as fine Wink



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
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 ... Wink


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
Focal-Point perhaps? Big Grin



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Guru
posted Hide Post
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


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Guru
posted Hide Post
quote:
The 'F' in RTFM could also mean something quite different ...

Respect The Female Members, of course.


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Expert
posted Hide Post
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.


Waz...

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

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Guru
posted Hide Post
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


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Expert
posted Hide Post
All comes down to what you need to do....


Waz...

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

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Eliminate old records

Copyright © 1996-2020 Information Builders