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,

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

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,

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

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,

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