Hi All,

I have a report request.
Suppose the source data is:
Name Effective_dt Status
N1 May 2,2009 Failed
N1 April 1,2009 Failed
N1 March 28,2009 Succeed
N1 March 20,2009 Failed
N1 March 10,2009 Succeed
N1 March 8,2009 Failed
N2 May3,2009 Failed
N2 April 2,2009 Failed

The output should be:
N1 May 2,2009 Failed
N1 April 1,2009 Failed
N2 May3 ,2009 Failed
N2 April 2,2009 Failed

Basically, the report should show all the failed records after a most recent succeed for each Name. If none of the record for a name is Succeed, it will include all. I hope my requirement description is clear.

Any suggestions on this?

Thanks and Regards,

Alu

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

Suggested approach:

Using suitable defines and a multiverb request, count successes by name, and Print records (and a Compute discussed below) by name by date.

The compute: assign a sequence number, resetting it with name, and bumping it when the record is a Success.

- If there are no successes for a given Name, then all its records are failures, and all have zero as their sequences number.
- If there are any successes, all failure records are sequenced to indicate the most recent success number, with the last group's sequence number matching the name's Count of successes.

Either way, you want to print all of a Name's Failure records whose seq matches the Name's Success count. An IF TOTAL or WHERE TOTAL will do that.

I took the liberty of reformatting the data to be able to use it

Take a look at this

 FILEDEF STATUS DISK STATUS.FTM (APPEND
FILEDEF MSTATUS DISK STATUS.MAS (APPEND
-RUN
-WRITE STATUS N1 May 2,2009        Failed
-WRITE STATUS N1 April 1,2009      Failed
-WRITE STATUS N1 March 28,2009     Succeed
-WRITE STATUS N1 March 20,2009     Failed
-WRITE STATUS N1 March 10,2009     Succeed
-WRITE STATUS N1 March 8,2009      Failed
-WRITE STATUS N2 May 3,2009        Failed
-WRITE STATUS N2 April 2,2009      Failed
-*
-WRITE MSTATUS FILE=STATUS, SUFFIX=FIX,$
-WRITE MSTATUS  SEGNAME=STATUS, SEGTYPE=S0
-WRITE MSTATUS FIELD=NAME,ALIAS=E01,ACTUAL=A2,USAGE=A2,$
-WRITE MSTATUS FIELD=F1,ALIAS=E02,ACTUAL=A1,USAGE=A1,$
-WRITE MSTATUS FIELD=DATE,ALIAS=E03,ACTUAL=A17,USAGE=A17,$
-WRITE MSTATUS FIELD=F2,ALIAS=E04,ACTUAL=A1,USAGE=A1,$
-WRITE MSTATUS FIELD=STATUS,ALIAS=E05,ACTUAL=A7,USAGE=A7,$
-WRITE MSTATUS DEFINE MONTH/A9=GETTOK(DATE, 17, 1, ' ', 9, 'A9');,$
-WRITE MSTATUS DEFINE IMONTH/I2=DECODE MONTH('January' 1 'February' 2 'March' 3 'April' 4
-WRITE MSTATUS                               'May' 5 'June' 6 'July' 7 'August' 8
-WRITE MSTATUS                               'September' 9 'October' 10 'November' 11 'December' 12 ELSE 99);,$
-WRITE MSTATUS DEFINE DAY/A2=GETTOK((GETTOK(DATE, 17, 1, ',', 13, 'A13')), 13, 2, ' ', 2, 'A2');,$
-WRITE MSTATUS DEFINE YEAR/A4=GETTOK(DATE, 17, -1, ',', 4, 'A4');,$
-*
TABLE FILE STATUS
PRINT DAY MONTH YEAR
BY NAME
BY HIGHEST STATUS
BY  YEAR
BY  IMONTH
BY  DAY
ON TABLE HOLD AS SORTED FORMAT FOCUS
END
-RUN
FILEDEF REQUIRED DISK REQUIRED.FTM (APPEND
-RUN
TABLE FILE SORTED
SUM MAX.STATUS NOPRINT
    COMPUTE ALLFAILED/A1= IF C1 EQ 'Failed' THEN 'Y' ELSE 'N'; NOPRINT
BY NAME NOPRINT
PRINT NAME DAY MONTH IMONTH YEAR STATUS
WHERE TOTAL ALLFAILED EQ 'Y'
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS REQUIRED FORMAT ALPHA
END 
-RUN
TABLE FILE REQUIRED
BY NAME
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE
END
-RUN
TABLE FILE SORTED
SUM LST.DAY LST.IMONTH LST.YEAR 
   COMPUTE KEY1/A8=C3 || EDIT(C2) || C1; 
BY NAME
WHERE STATUS EQ 'Succeed'
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS WANTED FORMAT FOCUS INDEX NAME
END
-RUN
JOIN NAME IN SORTED TO NAME IN WANTED AS J1
DEFINE FILE SORTED
IDAY/I2=EDIT(DAY);
KEY2/A8=YEAR || EDIT(IMONTH) || EDIT(IDAY);
END
TABLE FILE SORTED
PRINT DAY MONTH IMONTH YEAR STATUS
BY NAME
BY STATUS NOPRINT
WHERE NOT NAME IN  FILE SAVE
WHERE STATUS EQ 'Failed'
WHERE KEY2 GE KEY1
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS REQUIRED
END
-RUN
TABLE FILE REQUIRED
PRINT  
    COMPUTE DATE/A17=MONTH | DAY | ',' | YEAR; 
    COMPUTE IDAY/I2=EDIT(DAY); NOPRINT
    STATUS
BY NAME
BY HIGHEST YEAR NOPRINT
BY HIGHEST IMONTH NOPRINT
BY HIGHEST IDAY NOPRINT
END

 

You could reduce your processing by converting the DATE field to a "real" date field (if it isn't a smart date already) and then parse the data twice -

Using the begining of JG's code

-WRITE MSTATUS DEFINE STAT_DATE/HYYMDS = HINPUT(17, STRREP(17, DATE, 1, ',', 1, ' ', 17, 'A17'), 8, STAT_DATE);
-RUN
TABLE FILE STATUS
BY NAME
BY HIGHEST 1 STAT_DATE AS MAX_SUCCEED
WHERE STATUS EQ 'Succeed'
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS WANTED FORMAT FOCUS INDEX NAME
END
-RUN
JOIN CLEAR *
JOIN LEFT_OUTER NAME IN STATUS TO NAME IN WANTED AS J1
TABLE FILE STATUS
PRINT *
BY NAME
WHERE STATUS EQ 'Failed'
WHERE STAT_DATE GT MAX_SUCCEED
   OR MAX_SUCCEED IS MISSING
ON TABLE SET HOLDLIST PRINTONLY
END
-RUN

T

I wasn't going to give away the store -- but what I originally suggested requires just one pass:

-****** setup ********
EX -LINES 7 EDAPUT MASTER,TRIALS,CV,FILE
FILE=TRIALS,SUFFIX=FOC,$
SEGMENT=ONE,SEGTYPE=S1,$
FIELD=NAME ,,A10,$
SEGMENT=TWO,SEGTYPE=S1,PARENT=ONE,$
FIELD=DATE ,,MDYY,$
FIELD=RESULT,,A8,$
-RUN
CREATE FILE TRIALS
MODIFY FILE TRIALS
FREEFORM NAME DATE RESULT
MATCH * KEYS
ON NOMATCH INCLUDE
ON MATCH REJECT
DATA
N1,5/2/2009,Failed,$
N1,4/1/2009,Failed,$
N1,3/28/2009,Succeed,$
N1,3/20/2009,Failed,$
N1,3/10/2009,Succeed,$
N1,3/8/2009,Failed,$
N2,5/3/2009,Failed,$
N2,4/2/2009,Failed,$
END
-RUN

-******** report *********
DEFINE FILE TRIALS
  SUCCESS/I3S= RESULT EQ 'Succeed';
  FAILURE/I3S=NOT SUCCESS;
END
TABLE FILE TRIALS
  WRITE SUM.SUCCESS 
          NOPRINT
    BY NAME
  PRINT RESULT
        SUCCESS
          NOPRINT
      AND COMPUTE SEQ  /I3= SUCCESS + (NAME EQ LAST NAME)*(LAST SEQ);
          NOPRINT
      AND COMPUTE TAKE/I3S= FAILURE AND ( SEQ EQ C1 );
          NOPRINT
    BY NAME BY DATE
  IF TOTAL TAKE IS TRUE
END

Comment out the NOPRINTs and the IF TOTAL to see how it operates.

Thank you all.
I am eager to try JG's apporach. When I copied the code to my Webfocus studio, I got error message "(FOC205) The Description cannot be found for file named:status". I did a search, it may be because I didn't use the FILEDEF command properly.

For this report request, I have SQL query can do it(see following code, I simply it a litter bit for easy to post).

[CODE]
SELECT name, effective_dt,status
FROM TABLE_ITEM item,
where STATUS LIKE '%Failure'
AND effective_dt BETWEEN TO_DATE('2007-11-27 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND TO_DATE('2007-11-27 23:59:59','YYYY-MM-DD HH24:MI:SS')
AND (TO_DATE(effective_dt,'DD-MM-YYYY HH24:MI:SS') >
(
SELECT TO_DATE(MAX(item_inner.oi_effective_dt),'DD-MM-YYYY HH24:MI:SS')
FROM table_item item_inner,
AND item_inner.STATUS LIKE '%Success'
AND item_inner.name = item.name
)
OR NOT EXISTS
(
SELECT 1
FROM table_item item_inner1
item_inner1.STATUS LIKE '%Success'
AND item_inner1.name = item.name
)
)

[CODE]

But it is so so so slow even when I only have a date range of one day and run it in TOAD. Actually, not just slow, it make the TOAD dead there.

I am hopeing if I do it in WebFOCUS like your guys's suggestion, it will be better. I will try and let you know.

Thanks and Regards

alu

quote:

but what I originally suggested requires just one pass

Nice solution Jack and just goes to show that there's more than one way to skin a cat (no animals were injured in that statement ).

Alu,

Perhaps if you do not grasp how to run JG's or the other code samples then perhaps a visit to first base and some training might be in order? Try running as is to start and then try and understand what it is achieving then take what you learn and apply it to your situation.

BTW, if your code is killing toad then, again, go back to basics.

T

Hi All,

WebFOCUS is terrific and you guys are genius.
I can't believe my eyes when I get my expecting
results with the few line of codes from j.gross.

Thank you very very much. It seems like I can have a good sleep tonight. Yesterday I didn't.

Or even more succinctly,

-******** report *********
DEFINE FILE TRIALS
  SUCCESS/I3S= RESULT EQ 'Succeed';
  FAILURE/I3S=NOT SUCCESS;
END
TABLE FILE TRIALS
  WRITE SUM.SUCCESS      NOPRINT
    BY NAME
  PRINT RESULT
        SUCCESS          NOPRINT
        FAILURE          NOPRINT
      AND COMPUTE SEQ  /I3= SUCCESS + (NAME EQ LAST NAME)*(LAST SEQ);          NOPRINT
    BY NAME 
    BY TOTAL HIGHEST 1 SEQ
    BY DATE
  IF TOTAL FAILURE IS TRUE
END

("If Total Failure..." -- I couldn't resist.)

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

quote:

TOTAL FAILURE

ROFL

Very topical

T