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] Grouping of Data

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Grouping of Data
 Login/Join
 
Platinum Member
posted
Hi

I am working on a report and able to gather all data. But I am having problem grouping them as follows. Can someone suggest me a way to do this?

Data in HOLD File after joins and queries. 
						
ID	DATE	        TRANS	DEP     NAME	P1	P2
S1	4/26/2012	Local	C	King	VY1	S1A
S1	4/26/2012	Local	C	Prince	VY1	S1A
S1	4/26/2012	Local	C	King	VY2	S1A
S1	4/26/2012	Local	C	Prince	VY2	S1A
S1	4/26/2012	Local	C	King	VY1	S1B
S1	4/26/2012	Local	C	Prince	VY1	S1B
S1	4/26/2012	Local	C	King	VY2	S1B
S1	4/26/2012	Local	C	Prince	VY2	S1B

Output format needed:
ID is unique field and there will be multiple IDs but each ID should have only 1 record as follows

ID	DATE	        TRANS	DEP	  NAME	        P1	   P2
S1	4/26/2012	Local	C	  King,Prince	VY1,VY2	   S1A,S1B
						


Guide me thru a way

Thank you

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


WF 8.2.01 APP STUDIO
PDF,HTML,EXL2K,Active
 
Posts: 139 | Registered: July 21, 2011Report This Post
Virtuoso
posted Hide Post
Something like this:
DEFINE FILE HOLD
SAME/I1= (ID    EQ LAST ID) 
     AND (DATE  EQ LAST DATE) 
     AND (TRANS EQ LAST TRANS);
DEPLIST/A100V=IF (SAME) 
  THEN ( (LAST DEPLIST) || (',',DEP) )
  ELSE DEP;

[and similarly NAMELIST based on NAME, and PLIST based on P1.]

END
TABLE FILE HOLD
WRITE 
  LST.DEPLIST AS DEP
  etc
BY ID 
BY DATE 
BY TRANS
END
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Platinum Member
posted Hide Post
Hi Gross

Thanks for your reply. When I do this, it does group the data but it does not remove duplicates.
King comes 3 times, Prince comes 3 time etc.
Can u help me on that?

Thank you
quote:
Originally posted by j.gross:
Something like this:
DEFINE FILE HOLD
SAME/I1= (ID    EQ LAST ID) 
     AND (DATE  EQ LAST DATE) 
     AND (TRANS EQ LAST TRANS);
DEPLIST/A100V=IF (SAME) 
  THEN ( (LAST DEPLIST) || (',',DEP) )
  ELSE DEP;

[and similarly NAMELIST based on NAME, and PLIST based on P1.]

END
TABLE FILE HOLD
WRITE 
  LST.DEPLIST AS DEP
  etc
BY ID 
BY DATE 
BY TRANS
END

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


WF 8.2.01 APP STUDIO
PDF,HTML,EXL2K,Active
 
Posts: 139 | Registered: July 21, 2011Report This Post
Gold member
posted Hide Post
Try this modification to jgross' idea.

 
DEFINE FILE HOLD
-* USED SOFT Concatentation ... then had to remove the spaces
DEPLIST/A100V= IF (DEPLIST OMITS NAME) THEN ( (LAST DEPLIST) | (','|NAME) )
ELSE LAST DEPLIST;
DEPLST/A100V=  STRREP(100,DEPLIST,1,' ',0,'X',100,DEPLST);
XP1/A100V= IF (XP1 OMITS P1) THEN ( (LAST XP1) || (','|P1) )
ELSE LAST XP1;
XP2/A100V= IF (XP2 OMITS P2)  THEN ( (LAST XP2) || (','|P2) )
ELSE LAST XP2;
END

TABLE FILE HOLD
SUM
-* These Computes were to remove the FIRST COMMA
COMPUTE XDEPLST/A100V = SUBSTV(100,SQUEEZ(100, DEPLST, 'A100'), 2, 100, XDEPLST); AS 'NAME'
COMPUTE ZP1/A100V     = SUBSTV(100, XP1, 2, 100, ZP1); AS 'P1'
COMPUTE ZP2/A100V     = SUBSTV(100, XP2, 2, 100, ZP2); AS 'P2'
BY ID
BY DATE
BY TRANS
BY DEP
END


WF 7.6.10, Windows, PDF, Excel
 
Posts: 75 | Location: Dallas, TX | Registered: February 12, 2004Report This Post
Virtuoso
posted Hide Post
If I understand correctly, you want to include a list of the *distinct* values of DEP, and similarly of NAME and P1 -- per unique combination of the three key fields (ID, Date, Trans).

Hold the list of distinct values of DEP
(
TABLE FILE HOLD
BY ID BY DATE BY TRANS
BY DEP
ON TABLE HOLD AS HOLD1
END
)
and similarly create HOLD2 and 3 for distinct values of NAME and P1, resp.

Define DEPLIST, NAMELIST and PLIST (As in my earlier post) for HOLD1, 2 and 3 resp.

Finally, use MATCH FILE to combine the keys fields with the three List variables in the three Hold files.

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
Platinum Member
posted Hide Post
Hi Mary

this works great..
thanks Mary n Gross for the help.
quote:
Originally posted by Mary Watermann:
Try this modification to jgross' idea.

 
DEFINE FILE HOLD
-* USED SOFT Concatentation ... then had to remove the spaces
DEPLIST/A100V= IF (DEPLIST OMITS NAME) THEN ( (LAST DEPLIST) | (','|NAME) )
ELSE LAST DEPLIST;
DEPLST/A100V=  STRREP(100,DEPLIST,1,' ',0,'X',100,DEPLST);
XP1/A100V= IF (XP1 OMITS P1) THEN ( (LAST XP1) || (','|P1) )
ELSE LAST XP1;
XP2/A100V= IF (XP2 OMITS P2)  THEN ( (LAST XP2) || (','|P2) )
ELSE LAST XP2;
END

TABLE FILE HOLD
SUM
-* These Computes were to remove the FIRST COMMA
COMPUTE XDEPLST/A100V = SUBSTV(100,SQUEEZ(100, DEPLST, 'A100'), 2, 100, XDEPLST); AS 'NAME'
COMPUTE ZP1/A100V     = SUBSTV(100, XP1, 2, 100, ZP1); AS 'P1'
COMPUTE ZP2/A100V     = SUBSTV(100, XP2, 2, 100, ZP2); AS 'P2'
BY ID
BY DATE
BY TRANS
BY DEP
END


WF 8.2.01 APP STUDIO
PDF,HTML,EXL2K,Active
 
Posts: 139 | Registered: July 21, 2011Report 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] Grouping of Data

Copyright © 1996-2020 Information Builders