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] How to compare values across rows without having to create many defines

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] How to compare values across rows without having to create many defines
 Login/Join
 
axion
Member
posted
Hi all,

I have a field that contains a course number ENGLISH 1000 , for example. Now I need to make sure that the list of values for a particular student are unique.
EXAMPLE

STUDENT_ID SLOTNUM CRSNUM
123 1 ENGL1000
123 2 THEO1000
123 3 MATH1000
123 4 BIOG1000
123 5 ENGL1000



As you can see on the table above the student has ENGL1000 on both Slot 1 and 5. In order for me to determine that I would have to have 5 define fields (1 per slot) in which I compare the slots crsnum to the crsnum in all of the previous slots. Unfortunately this would leave me with a denormalized record which I will then have to normalize again into rows. Is there a better way to solve this problem? If I go the denomalization route it will break if a student has more than 5 slots.


-Thanks

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


WF 7.6.10, Windows, Banner 8, Oracle 10g.
 
Posts: 26 | Registered: February 04, 2009Report This Post
Waz
Expert
posted Hide Post
What you can do is check for differences in max or min amounts or do counts.
EX -LINES 6 EDAPUT MASTER,DUPES,CV,FILE
FILENAME=DUPES, SUFFIX=FIX,$
SEGNAME=DUPES, $
  FIELD=STUDENT_ID ,ALIAS=  ,I9 ,A4 ,$
  FIELD=SLOTNUM    ,ALIAS=  ,I9 ,A2 ,$
  FIELD=CRSNUM     ,ALIAS=  ,A8 ,A8 ,$

EX -LINES 6 EDAPUT FOCTEMP,dupes,CV,FILE
123 1 ENGL1000
123 2 THEO1000
123 3 MATH1000
123 4 BIOG1000
123 5 ENGL1000

FILEDEF DUPES DISK dupes.ftm (LRECL 14

TABLE   FILE DUPES
 SUM    MAX.SLOTNUM AS MAX_SLOTNUM
        MIN.SLOTNUM AS MIN_SLOTNUM
        CNT.SLOTNUM AS CNT_SLOTNUM
 BY     STUDENT_ID
 BY     CRSNUM
 PRINT  SLOTNUM
 BY     STUDENT_ID
 BY     CRSNUM
-* ON     TABLE HOLD AS TMP_DUPE
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
EWoerle
Gold member
posted Hide Post
axion,

you can use the last command to check for duplicates. What you would want to do is sort your data by student id and course num and then hold it. take a second pass at the data and then in the define do something along the lines of:

 check_dupes/A1=IF LAST STUDENT_ID EQ STUDENT_ID AND LAST CRSNUM EQ CRSNUM THEN 1 ELSE 0;


Eric Woerle
WF 7.6.7 Reportting Server
ETL 7.6.10
Dev Studio 7.6.7
 
Posts: 95 | Registered: July 31, 2007Report This Post
j.gross
Virtuoso
posted Hide Post
To list the duplicated course numbers (if any) within student number, Count and If Total is all you need.

table file ...
write cnt.crsnum by student_id by crsnum
if total cnt.crsnum gt 1
...


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
axion
Member
posted Hide Post
EWoerle,

Thanks for the advice but LAST is of limited use in this since I must compare the value to all of the previous members not just the immediate one. But I will look into how sorting the crnsnums might influence such an algorithm. It might allow me to make certain assumptions.


-Jose Deleon


WF 7.6.10, Windows, Banner 8, Oracle 10g.
 
Posts: 26 | Registered: February 04, 2009Report This Post
axion
Member
posted Hide Post
Hi All,

I think I may have over simplified the problem when posting it.

Each slot as the course number but that course number can appear multiple times per student due to other data which I need to keep.

For Example,

123 1 ENGL1000 alt1
123 2 THEO1000 alt1
123 3 MATH1000 alt1
123 4 BIOG1000 alt1
123 4 BIOG1000 alt2
123 4 BIOG1000 alt3
123 4 BIOG1000 alt4
123 5 ENGL1000 alt1

This is fine because basically the way the structure works is you have a primary course num which is tied to multiple alternate course numbers for that same slot number. My task is to make sure that the primary course numbers do not replicate across the slots while still keeping the alternate data. If there is a duplication then I must take the primary and replace it with the first alternate crns number for that slot.

I'll try some of the advice already given.

-Thanks.


WF 7.6.10, Windows, Banner 8, Oracle 10g.
 
Posts: 26 | Registered: February 04, 2009Report This Post
axion
Member
posted Hide Post
Waz,

Thanks for the example. I see how I can use this. I'll try it and let you know how it goes.


-Axion


WF 7.6.10, Windows, Banner 8, Oracle 10g.
 
Posts: 26 | Registered: February 04, 2009Report This Post
j.gross
Virtuoso
posted Hide Post
Aha! So you can tolerate multiple rows with the same course#, as long as all have the same slot number; you want to identify students and courses where the course appears in multiple slots.

This approach should work:
table ...
write 
 min.slot 
 max.slot 
and compute 
 range/i3=max.slot - min.slot;  noprint
by student by course
where total range ne 0;
...

This message has been edited. Last edited by: j.gross,


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Stefaans
Silver Member
posted Hide Post
Hi I have not had time to read all the learned answers. This is how I would handle it. Create a DEFINE called concatvar a50 whatever and concatenate all fields together. A number would need an edit. alpha1||edit(number)|| alpha
table file..
sum concatvar by concatvar noprint
on table hold as
end


WebFOCUS 7.7.01 Windows, TSO 7.0
 
Posts: 43 | Registered: April 18, 2007Report This Post
axion
Member
posted Hide Post
Waz,

I have tried your solution and it worked PERFECTLY! At first glance it looks like j.gross's solution is identical. This solution allows me to find the values and substitute them. Because it only looks at the max and min I use dialogue manager to loop multiple times just in case the course appears more than twice in the file for the student. It reminds me of a binary sort.

-Axion


WF 7.6.10, Windows, Banner 8, Oracle 10g.
 
Posts: 26 | Registered: February 04, 2009Report This Post
axion
Member
posted Hide Post
Thank you all for your help.

It has broaden my knowledge on techniques to use.
Very helpful.

Thanks.


WF 7.6.10, Windows, Banner 8, Oracle 10g.
 
Posts: 26 | Registered: February 04, 2009Report 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] How to compare values across rows without having to create many defines

Copyright © 1996-2020 Information Builders