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 divide rows by date sequence in webfocus

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]How to divide rows by date sequence in webfocus
 Login/Join
 
Member
posted
I have the data below returned by my sql

TYPE END_DATE SEQUENCE
MID 10-MAR-15 2
MID 09-MAR-15 1
MID 04-MAR-15 3
MID 03-MAR-15 2
MID 02-MAR-15 1

As you can see from the data above, bottom 3 rows are part of the same sequence i.e they were created on 2nd, 3rd and 4th march hence there count is 1,2 and 3. Then 9th and 10th March is the second sequence. How can I write code in webfocus code so that it returns me highest row of every sequence. So from the data above, the report should show two rows

TYPE END_DATE SEQUENCE
MID 10-MAR-15 2
MID 04-MAR-15 3

Another twist to the above problem is the user has the abililty to search by date. So if someone searches for FROMDATE=1ST MARCH , TO DATE=3RD March then the report should return one row i.e. the highest row for that sequence

TYPE END_DATE SEQUENCE
MID 03-MAR-15 2

Below is my webfocus code but it only returns the highest sequence ignores the date sequence.

TABLE FILE sequence
SUM
MAX.SEQUENCE
MAX.END_DATE/HDMYY AS 'END DATE'
BY TYPE

Also to add to this there will not be any row inserted on a weekend. Any help would be appreciated!

This message has been edited. Last edited by: <Emily McAllister>,


WebFOCUS 8.0.0.9
Linux 2.6.32-220.el6.x86_64
 
Posts: 9 | Registered: March 23, 2015Report This Post
Virtuoso
posted Hide Post
Try this two-step approach:
-* identify consecutively-dated records
table file x
 print *
  compute group/i6= if date eq (last date)+1 then (last group)+1 else (last group);
  compute seq/i6= if group eq (last group) then (last seq) + 1 else 1;     
  by date
on table hold as y
end
-* pull record with latest date (highest seq) within each group
table file y
 write max.seq noprint
  by group noprint
 print *
  by group noprint
 where total seq eq max.seq;
end

This assumes date has a "smart date" format, like yymd.
(I used lower-case to indicate the code is untested)
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Member
posted Hide Post
I first tried the compute code as below

TABLE FILE STALE_HOLD
PRINT *
COMPUTE GROUP/I6= IF END_DATE/YYMD EQ (LAST END_DATE/YYMD)+1 THEN (LAST GROUP)+1 ELSE (LAST GROUP);
COMPUTE SEQ/I6= IF GROUP EQ (LAST GROUP) THEN (LAST SEQ) + 1 ELSE 1;
BY END_DATE/YYMD
END


When I try to run the code I get the error below

localizeddesc="Abnormal termination of reporting server agent (Crashed or extreme operator kill)" name="runAdHocFex" returncode="32027" returndesc="IBFSException 32027: probable server crash (unknown error 0)"


WebFOCUS 8.0.0.9
Linux 2.6.32-220.el6.x86_64
 
Posts: 9 | Registered: March 23, 2015Report This Post
Virtuoso
posted Hide Post
This assumes that the SEQUENCE values are always sorted in descending order (as in your sample data):

DEFINE FILE xxx
 GROUP/I9 = IF (LAST GROUP EQ 0) THEN 1 ELSE
            IF (LAST SEQUENCE EQ 1) THEN (LAST GROUP + 1) ELSE (LAST GROUP);
END
-*
TABLE FILE xxx
 SUM MAX.END_DATE
 BY TYPE
 BY GROUP  NOPRINT
 BY HIGHEST 1 SEQUENCE
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Member
posted Hide Post
Ok guys after correcting some school boy errors (im very new to webfocus) and using information given by the experts I have written the code below

TABLE FILE STALE_PRICE_HOLD
COMPUTE PRC_TMS = PRC_TMS/HDMYY;
PRINT *
ON TABLE HOLD AS HOLD1

-RUN
DEFINE FILE HOLD1
GROUP/I9 = IF (LAST GROUP EQ 0) THEN 1 ELSE
IF (STALE_PRC_PRD_QTY EQ LAST STALE_PRC_PRD_QTY + 1 AND PRC_TYP EQ LAST PRC_TYP)
THEN (LAST GROUP) ELSE (LAST GROUP + 1);
END

-RUN
TABLE FILE HOLD1
PRINT
SUM MAX.PRC_TMS
BY PRC_TYP
BY GROUP NOPRINT
BY HIGHEST 1 STALE_PRC_PRD_QTY

When I run it on the data below

PRC_TYP PRC_TMS STALE_PRC_PRD_QTY
BID 06-MAR-15 2
MID 06-MAR-15 2
ASKED 06-MAR-15 2
ASKED 05-MAR-15 1
MID 05-MAR-15 1
BID 05-MAR-15 1
MID 25-FEB-15 2
BID 25-FEB-15 2
ASKED 25-FEB-15 2
MID 24-FEB-15 1
BID 24-FEB-15 1
ASKED 24-FEB-15 1

I am getting the result below

ASKED 2015/03/06 2
ASKED 2015/03/05 1
ASKED 2015/02/25 2
ASKED 2015/02/24 1
BID 2015/03/06 2
BID 2015/03/05 1
BID 2015/02/25 2
BID 2015/02/24 1
MID 2015/03/06 2
MID 2015/03/05 1
MID 2015/02/25 2
MID 2015/02/24 1

My desired result is below

ASKED 2015/03/06 2
BID 2015/03/06 2
MID 2015/03/06 2
ASKED 2015/02/25 2
BID 2015/02/25 2
MID 2015/02/25 2

Any help would be appreciated experts! thanks..

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


WebFOCUS 8.0.0.9
Linux 2.6.32-220.el6.x86_64
 
Posts: 9 | Registered: March 23, 2015Report This Post
Master
posted Hide Post
You have several errors.

In your first COMPUTE you need the variable typing to be on the left side of the equation, not the right:

COMPUTE PRC_TMS/HDMYY=PRC_TMS;

In your DEFINE it looks like you want to select the LAST group number if certain conditions are met or otherwise increment the group number by 1.

Everything in a DEFINE happens at the individual record level, so if you want to assign the GROUP value and subsequently use it for selection purposes you will have to make a HOLD file that contains the newly-acquired GROUP value and then select on that.

Notice that Jack (j.gross) uses COMPUTE and not DEFINE. COMPUTE operates on the accumulated internal data matrix and not the raw incoming data, so it is probably the tool to use in this case.

WebFOCUS does have a GROUPED BY function and you may want to take a look at that.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Virtuoso
posted Hide Post
Asad,

Try something like this:
  
-* File asad01.fex
-* Creating an example of the file
-* Assumption: the file is sorted by date and by sequence 
TABLE FILE CAR
LIST MPG
BY COUNTRY
IF COUNTRY NE FRANCE
ON TABLE HOLD
END
DEFINE FILE HOLD
STDATE/DMYY='01012015';
SEQUENCE/I5=LIST;
SKIP/I5=IF LAST LIST EQ 0 THEN 1 ELSE LAST SKIP + 10;
END_DATE/DMYY=IF SEQUENCE EQ 1 THEN STDATE + SKIP ELSE LAST END_DATE + 1;
END
TABLE FILE HOLD
PRINT END_DATE SEQUENCE
ON TABLE HOLD AS ASAD
END
-RUN
-* Create groups
DEFINE FILE ASAD
GROUP/I5=IF SEQUENCE EQ 1 THEN GROUP + 1 ELSE GROUP;
END
-RUN
-* Take the highest sequence number within the group
TABLE FILE ASAD
SUM END_DATE 
BY GROUP 
BY HIGHEST 1 SEQUENCE
END


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
Master
posted Hide Post
Ooops - my bad.

In my post above I mentioned GROUPED BY - which doesn't exist to my recently awakened knowledge.

What I meant was IN-GROUPS-OF which does exist.

However Danny has shown a way, which is pretty much what I talked about - running a report to capture the values of SEQUENCE and then HOLDing the result. Once the SEQUENCE values are available then grouping is possible.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Member
posted Hide Post
Thanks all of you! I got to learn a lot from this thread. From all of your help above I managed to solve the issue by playing around with the sequence. Logic is that if sequence is eq last sequence +1 then they are part of the same group. In our database the sequence resets to 0 if its a new sequence so code below works well Big Grin

-RUN
TABLE FILE STALE_PRICE_HOLD
PRINT *
WHERE PRC_TMS GE '&FTMS'
WHERE PRC_TMS LE '&TTMS'
ON TABLE HOLD AS HOLD1

-RUN
TABLE FILE HOLD1
PRINT *
COMPUTE GROUP/I9 = IF (LAST GROUP EQ 0) THEN 1 ELSE
IF (STALE_PRC_PRD_QTY EQ LAST STALE_PRC_PRD_QTY + 1 AND PRC_TYP EQ LAST PRC_TYP)
THEN (LAST GROUP) ELSE (LAST GROUP + 1);
BY PRC_TYP NOPRINT
ON TABLE HOLD AS HOLD2

-RUN
TABLE FILE HOLD2
PRINT
SUM MAX.PRC_TMS
BY PRC_TYP
BY GROUP NOPRINT
BY HIGHEST 1 STALE_PRC_PRD_QTY


Still need to learn more about how can I convert a date returned from sql db to a webfocus smart date and then how to increment dates that it automatically skips weekend so e.g. if its a friday and I want the next day to be monday how I can achieve that. But for now just playing around with sequence number works fine!. Really appreciate all your help guys!

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


WebFOCUS 8.0.0.9
Linux 2.6.32-220.el6.x86_64
 
Posts: 9 | Registered: March 23, 2015Report This Post
Guru
posted Hide Post
If you want to invest in your WebFOCUS education, get a copy of Almost 1001 Ways to Work with Dates in WebFOCUS; it will address most, if not all, of your questions regarding dates.

HTH


jimster06
DevStu WF 7.6.11
W7
HTML, PDF, EXL2K
 
Posts: 252 | Location: USA | Registered: April 15, 2003Report 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 divide rows by date sequence in webfocus

Copyright © 1996-2020 Information Builders