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

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)

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)"

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

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,

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.

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

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.

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

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

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