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     Comparison of a record with the next record.

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Comparison of a record with the next record.
 Login/Join
 
Silver Member
posted
I have three fields as follow
roll no. comment no. comment1 comment2
-------- ---------- --------- --------
1234 01 aaa
1234 01 yyy
1234 02 yyy bbb
1234 01 ccc
1234 01 xxx

As shown above there can be many records for a particular roll no..The field comment no. can hold value either '01' or '02'.
If the comment no is '02' then i need to eliminate the previous record.

Now in the output I want it as-
roll no. comment no. comment1 comment2
-------- ---------- --------- --------
1234 01 aaa
1234 02 yyy bbb
1234 01 ccc
1234 01 xxx

I tried to assign an indicator field based on the comment no. using 'last' verb. Using 'last' verb we can compare a record with previous record.
Is there any verb with which i can compare a record with next record.


FOCUS 7.1.1/ MF(OS/390)
 
Posts: 40 | Location: Chennai, India | Registered: January 31, 2007Report This Post
Virtuoso
posted Hide Post
Manash,
The only way you can accomplish this is by reversing the order of your file. It might become expensive if your file is very big. Anyway here is an example:
FILE
123401aaaddd
123401yyyeee
123402yyybbb
123401cccfff
123401xxxggg

MASTER
FILE=manash, SUFFIX=FIX, DATASET='C:\IBI\APPS\FOCALPOINT\MANASH.TXT'
SEGNAME=ROOT_SEG, SEGTYPE=S0, $
  FIELD=ROLL, ALIAS=ROLL, FORMAT=I4, ACTUAL=A4, $
  FIELD=CMT, ALIAS=CMT, FORMAT=I2L, ACTUAL=A2, $
  FIELD=CO1, ALIAS=CO1, FORMAT=A3, ACTUAL=A3, $
  FIELD=CO2, ALIAS=CO2, FORMAT=A3, ACTUAL=A3, $

FOCEXEC
TABLE FILE MANASH
LIST CMT CO1 CO2
BY ROLL
ON TABLE HOLD AS ONE FORMAT ALPHA
END

TABLE FILE ONE
PRINT E02 CO1 CO2
BY ROLL BY HIGHEST CMT
ON TABLE HOLD
END

DEFINE FILE HOLD
NXTCMT/I1=IF ROLL NE LAST ROLL THEN 1 ELSE IF LAST CMT NE 2 THEN 1 ELSE 0;
END
TABLE FILE HOLD
IF NXTCMT EQ 1
PRINT ROLL CMT CO1 CO1
BY ROLL NOPRINT BY E03 NOPRINT
END


Note: I use the LIST verb in order to retain the order of the original file.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Silver Member
posted Hide Post
Yes Danny my file is very big, Is there any other way?


FOCUS 7.1.1/ MF(OS/390)
 
Posts: 40 | Location: Chennai, India | Registered: January 31, 2007Report This Post
Virtuoso
posted Hide Post
Another approach is to build an exemption list in a DEFINE. Basically number each record with CNTR, and if the COMMENT_NO is 2, then add the previous CNTR value to field BLOCKSTRING (in this example). SUM BLOCKSTRING, and then have a TOTAL screen of BLOCKSTRING against CNTR.

DEFINE FILE Fn
CNTR/I4 WITH ROLL_NO = CNTR + 1;
BLOCK/A4 WITH CNTR = IF COMMENT_NO EQ 2 AND LAST COMMENT_NO EQ 1
                      THEN EDIT(LAST CNTR) ELSE '0';
BLOCKSTRING/A4000V = IF BLOCK NE '0'
                     THEN LAST BLOCKSTRING | '*' | BLOCK
                     ELSE LAST BLOCKSTRING;
END
TABLE FILE Fn
SUM BLOCKSTRING NOPRINT
PRINT ROLL_NO COMMENT_NO COMMENT1 COMMENT2
BY CNTR NOPRINT
WHERE TOTAL BLOCKSTRING OMITS EDIT(CNTR);
END


Works for the example you gave, and Manash, please update your signature so we know what products you are using.
Again may be limited by the number of records.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
Mmmm

This seems to work:
FILEDEF BLOCKF DISK C:\wherever\BLOCKF.DAT
DEFINE FILE Fn
CNTR/I8L WITH ROLL_NO = CNTR + 1;
BLOCK/A8 = IF COMMENT_NO EQ 2 AND LAST COMMENT_NO EQ 1
             THEN EDIT(LAST CNTR) ELSE '0';
BLOCKSTRING/I1 = IF BLOCK NE '0'
                     THEN PUTDDREC('BLOCKF',8,BLOCK,8,BLOCKSTRING)
                     ELSE 0;
END
TABLE FILE Fn
PRINT ROLL_NO COMMENT_NO COMMENT1 COMMENT2
BY CNTR NOPRINT
WHERE TOTAL NOT CNTR IN FILE BLOCKF;
END

Which should cope with more records, 16k allowed. Beware of the FILEDEF though, you may want ot point it to the temp directory.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
Manash

If COMMENT1 is a small field it can be used as a key field (which it in fact is in combination with the roll_no and comment_no)
Then you can do

TABLE FILE xxx
PRINT
COMMENT2
BY ROLL_NO
BY COMMENT1
BY HIGHEST 1 COMMENT_NO
END

But if the comment1 is a real comment this won't work.

Frank




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
Manash,

Are you limiting your file by any other selection? If not you could sort the file by 'roll no.' by highest 'comment no.'
add a compute:
KEEPIT/I2 = IF 'ROLL NO' NE LAST ROLL NO THEN 1 ELSE IF 'ROLL NO ' EQ LAST 'ROLL NO' THEN 0 ELSE 0;

And add

WHERE TOTAL KEEPIT EQ 1.

I put the 'roll no' in comments as I was not sure what the name was. I have to do this all the time with our student address records. The above assumes no matter how many comment numbers you only want the last one.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Silver Member
posted Hide Post
I tried out another logic to get my result and I am stuck with this.
I have the data as folows-

ROLlNO CNTR COMMENT COMMENT2 COMMENT3
------ ---- ------- -------- -------
91 1 aaa
91 1 bbb
91 1 ccc
91 1 ddd
91 1 eee
91 2 fff
91 3 ggg
91 1 hhh
91 1 iii

COMMENT2 and COMMENT3 are new fields defined by me in program using DEFINE.
Now If I have a CNTR field value as 2 the I need to move the respective COMMENT value to a
COMMENT2 field of the previous record and eliminate that record with CNTR = 2.
Similarly If the CNTR is 3 then I need to move the respective COMMENT value to the COMMENT3
field of the previous record with CNTR = 1 and eliminate this record with CNTR = 3.
I want the output as:

rollNO CNTR COMMENT COMMENT2 COMMENT3
------ ---- ------- -------- -------
91 1 aaa
91 1 bbb
91 1 ccc
91 1 ddd
91 1 eee fff ggg
91 1 hhh
91 1 iii

Any Suggestions?


FOCUS 7.1.1/ MF(OS/390)
 
Posts: 40 | Location: Chennai, India | Registered: January 31, 2007Report This Post
Virtuoso
posted Hide Post
Try this Manash.

  
DEFINE FILE Fn
COMMENT1/A30 = IF CNTR EQ 1 THEN COMMENT ELSE ' ';
COMMENT2/A30 = IF CNTR EQ 2 THEN COMMENT ELSE ' ';
COMMENT3/A30 = IF CNTR EQ 3 THEN COMMENT ELSE ' ';
NEWCNTR/I4 = IF CNTR EQ 1 THEN NEWCNTR+1 ELSE NEWCNTR;
END
TABLE FILE FN
WRITE
MAX.CNTR
MAX.COMMENT1
MAX.COMMENT2
MAX.COMMENT3
BY NEWCNTR
BY ROLL
END


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Silver Member
posted Hide Post
Thanx Alan for your suggestions, it solved my query.


FOCUS 7.1.1/ MF(OS/390)
 
Posts: 40 | Location: Chennai, India | Registered: January 31, 2007Report 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     Comparison of a record with the next record.

Copyright © 1996-2020 Information Builders