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]I'm trying to create a DEFINE based on a sequence of events

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]I'm trying to create a DEFINE based on a sequence of events
 Login/Join
 
Silver Member
posted
The DEFINE is 'TRUE_DATE/MDYY= ' it should capture the EVNT_DATE of the first occurrence of 'C'in the EVNT field
and if there is no 'C' then capture the EVNT_DATE of 'P'
I've tried the code below in many ways but I can't get it to work. I realized that it will only look at the EVNT
field one row at a time. How do I get it to look at all rows with same ID.

DEFINE
TRUE_DATE/MDYY=IF EVNT EQ 'C' THEN EVNT_DATE
ELSE IF EVNT NE 'C' AND EVENT EQ 'P' THE EVNT_DATE ELSE ' ';
END

The define doesn't work; it Populates TRUE_DATE for all EVNT EQ 'C' AND 'P'. I understand why it doesn't work but I
don't know what will make it work.

The output should produce the following results:
ID ITM EVNT SEQ EVNT_DATE TRUE_DATE
57 BLT P 1 09/30/2014
57 BLT C 2 09/30/2014 09/30/2014
57 BLT I 3 10/08/2014
57 BLT C 4 10/24/2014

60 BLT P 1 09/03/2014 09/03/2014
60 BLT I 3 10/08/2014

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


WebFOCUS 8206
Exl2k
 
Posts: 36 | Registered: May 23, 2014Report This Post
Platinum Member
posted Hide Post
quote:
09/03/2014

DEFINE
TRUE_DATE/MDYY=IF EVNT EQ 'C' THEN EVNT_DATE
ELSE IF EVNT NE 'C' AND EVENT EQ 'P' THE EVNT_DATE ELSE ' ';
END

------------------
In Your code , There is a IF - ELSE syntax error.

Post your condition, what you needed.


Thanks!
@vi

WebFOCUS 8105, Dev Studio 8105, Windows 7, ALL Outputs
 
Posts: 103 | Registered: July 08, 2013Report This Post
Silver Member
posted Hide Post
Sorry it was a typo but I do have the THEN in the actual code. I'm not getting an error just wrong results.

For ID 57 there are 4 rows. I want the define to place a date in only 1 row IF the following is true. If ID 57 has an EVNT EQ 'C' then populate TRUE_DATE with EVNT_DATE, the 'C' must be the first occurrence this can be determined by the SEQ field, then quit looking. Else IF there is no 'C' then look for 'P';

Does this require a match or some sort of loop?

I tried posting the desired result set. Hopefully that doesn't confuse you more.


WebFOCUS 8206
Exl2k
 
Posts: 36 | Registered: May 23, 2014Report This Post
Virtuoso
posted Hide Post
Don't know if it's what you're looking for, but take a look. At least it will be s starting point.

DEFINE FILE CAR
FND_SEDAN/A15 = IF COUNTRY EQ LAST COUNTRY THEN (IF BODYTYPE EQ 'SEDAN' THEN 'SEDAN FND' ELSE IF BODYTYPE EQ 'COUPE' THEN 'NOT FND' ELSE LAST FND_SEDAN)
               ELSE (IF BODYTYPE EQ 'SEDAN' THEN 'SEDAN FND' ELSE IF BODYTYPE EQ 'COUPE' THEN 'NOT FND' ELSE '');
END
TABLE FILE CAR
PRINT
   BODYTYPE
   FND_SEDAN
BY COUNTRY
END


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Virtuoso
posted Hide Post
It helps to mark output with CODE tags...

You want to produce the following results:
ID  ITM EVNT SEQ EVNT_DATE   TRUE_DATE
57  BLT P    1   09/30/2014
57  BLT C    2   09/30/2014  09/30/2014
57  BLT I    3   10/08/2014
57  BLT C    4   10/24/2014

60  BLT P    1   09/03/2014  09/03/2014
60  BLT I    3   10/08/2014


From your spec, you need to consider all C event rows before the P event rows, within each ID, in order to identify the "triggering" C or P row.
Define won't be of much use, since it considers the data rows in the order presented.

I assume that SEQ >0 and is unique within ID. Then the following should pin the correct TRUE_DATE value for each ID group to the triggering row, and leave it blank on the other rows.

TABLE FILE ...
PRINT
  ITM 
  EVNT 
  SEQ 
  EVNT_DATE  
  COMPUTE
    MARK_SEQ/I1 = 
      IF ( ID NE LAST ID ) AND ( LAST MARK_SEQ GT 0 ) THEN LAST_MARK_SEQ
      ELSE IF ( EVNT IN ('C','P') ) THEN SEQ 
      ELSE 0;
    TRUE_DATE/MDYY = IF MARK_SEQ EQ SEQ THEN EVNT_DATE ELSE ' ';

  BY ID SKIP-LINE
  BY TOTAL SEQ NOPRINT
  BY EVNT NOPRINT
  BY SEQ NOPRINT
END


I first sort the incoming rows by ID, EVNT (to place C's before P's), SEQ. The BY TOTAL SEQ rearranges the output into ID, SEQ order after the COMPUTEs have been processed.

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
Silver Member
posted Hide Post
j.gross I used the sample you posted and I'm almost there. The problem is the C and P and sometimes have the same EVNT_DATE so when that happens I get dups. Therefore MARK_SEQ EQ SEQ and both rows are captured. I've been trying to get around it for last two hours. What can I do to make it choose the C/(make it populate TRUE_DATE for the C row)?


WebFOCUS 8206
Exl2k
 
Posts: 36 | Registered: May 23, 2014Report This Post
Silver Member
posted Hide Post
My last post may have been confusing.....let me try again.

j.gross, I used the sample you posted. The problem is.... there are IDs with both C and P records P is SEQ 1 C is SEQ 2. They are both being marked therefore they are both getting a TRUE_DATE. How can I only get a TRUE_DATE for C when this happens?


WebFOCUS 8206
Exl2k
 
Posts: 36 | Registered: May 23, 2014Report This Post
Expert
posted Hide Post
BY ID SKIP-LINE
BY HIGHEST 1 SEQ NOPRINT
BY EVNT NOPRINT
BY SEQ NOPRINT


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Virtuoso
posted Hide Post
I find it easier to make two passes of the data. The first pass determines the TRUE_DATE and related ID/SEQ values.

SET HOLDLIST = PRINTONLY
SET ASNAMES  = ON
-* Write data to temp file
FILEDEF STARR_DATA DISK STARR_DATA.FTM
-RUN
-WRITE STARR_DATA 57BLTP 109/30/2014
-WRITE STARR_DATA 57BLTC 209/30/2014
-WRITE STARR_DATA 57BLTI 310/08/2014
-WRITE STARR_DATA 57BLTC 410/24/2014
-WRITE STARR_DATA 60BLTP 109/03/2014
-WRITE STARR_DATA 60BLTI 310/08/2014
-RUN
-* Create master to read temp data file.
FILEDEF MAST DISK STARR_DATA.MAS
-RUN
ID  ITM EVNT SEQ EVNT_DATE   TRUE_DATE
-WRITE MAST FILENAME=STARR_DATA, SUFFIX=FIX
-WRITE MAST  SEGNAME=SEG01, SEGTYPE=S0, $
-WRITE MAST   FIELDNAME=ID       ,, ACTUAL=A2 , USAGE=A2, $
-WRITE MAST   FIELDNAME=ITM      ,, ACTUAL=A3 , USAGE=A3, $
-WRITE MAST   FIELDNAME=EVNT     ,, ACTUAL=A1 , USAGE=A1, $
-WRITE MAST   FIELDNAME=SEQ      ,, ACTUAL=A2 , USAGE=I2, $
-WRITE MAST   FIELDNAME=EVNT_DATE,, ACTUAL=A10, USAGE=MDYY, $
-RUN
-* Determine TRUE_DATE for each ID value. Hold results in indexed FOCUS file.
TABLE FILE STARR_DATA
 SUM MIN.EVNT_DATE AS 'TRUE_DATE'
 BY ID
 BY LOWEST 1 EVNT
 BY LOWEST 1 SEQ
 WHERE (EVNT EQ 'C' OR 'P');
 WHERE (EVNT_DATE NE '');
 ON TABLE HOLD AS TRUE_DATES FORMAT FOCUS INDEX ID
END
-* Join original data to TRUE_DATE results and produce final report.
JOIN CLEAR *
JOIN ID IN STARR_DATA TO UNIQUE ID IN TRUE_DATES AS J1
-*
DEFINE FILE STARR_DATA
 TRUE_DATEX/MDYY = IF (STARR_DATA.SEQ EQ TRUE_DATES.SEQ) THEN TRUE_DATES.TRUE_DATE ELSE '';
END
-*
TABLE FILE STARR_DATA
 PRINT
  STARR_DATA.ID
  STARR_DATA.ITM
  STARR_DATA.EVNT
  STARR_DATA.SEQ
  STARR_DATA.EVNT_DATE
  TRUE_DATEX
 BY STARR_DATA.ID  NOPRINT
 BY STARR_DATA.SEQ NOPRINT
END


EDIT: Added BY LOWEST 1 SEQ.

This message has been edited. Last edited by: Dan Satchell,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Silver Member
posted Hide Post
I tried that earlier and it eliminated most of my output. It seems to need the BY TOTAL SEQ NOPRINT. I put the HIGHEST on the second BY HIGHEST SEQ.

BY TOTAL SEQ NOPRINT
BY EVNT NOPRINT
BY HIGHEST 1 SEQ NOPRINT

Still not working.


WebFOCUS 8206
Exl2k
 
Posts: 36 | Registered: May 23, 2014Report This Post
Silver Member
posted Hide Post
Thanks Dan. I knew this would result in my using Dialogue Manager. I guess I better learn it. OK I'll try to write this....I wish my data was as simple as the sample I put out there. I'll be checking in.


WebFOCUS 8206
Exl2k
 
Posts: 36 | Registered: May 23, 2014Report This Post
Expert
posted Hide Post
Hi Starr,
Dan has a given a good option, might try that.
I missed that you wanted both records...
Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Virtuoso
posted Hide Post
@ Starr: I only used Dialogue Manger to create the test data file and master. It won't be needed for the actual solution. The only part of my post you should need starts with the first TABLE FILE command.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Silver Member
posted Hide Post
Thanks...I realized that after I read the entire post. Will take a stab at it now.


WebFOCUS 8206
Exl2k
 
Posts: 36 | Registered: May 23, 2014Report This Post
Virtuoso
posted Hide Post
@ Starr: I made a change to my original post, indicated by the word EDIT at the bottom.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Virtuoso
posted Hide Post
quote:
I used the sample you posted and I'm almost there. The problem is the C and P and sometimes have the same EVNT_DATE so when that happens I get dups. Therefore MARK_SEQ EQ SEQ and both rows are captured.


My bad. Change ID NE ... to ID EQ ... :

MARK_SEQ/I1 = 
      IF ( ID EQ LAST ID ) AND ( LAST MARK_SEQ GT 0 ) THEN LAST_MARK_SEQ
      ELSE IF ( EVNT IN ('C','P') ) THEN SEQ 
      ELSE 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
Silver Member
posted Hide Post
Thanks to everyone for all your help. My issue is resolved and I learned a few things.

This forum is the best.


WebFOCUS 8206
Exl2k
 
Posts: 36 | Registered: May 23, 2014Report 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]I'm trying to create a DEFINE based on a sequence of events

Copyright © 1996-2020 Information Builders