Is there a way to combine the data to a single row based on some other sort values. I have data like this: Key Month 1 2 1 4 1 6 2 3 4 99
And I wat to show it like this Key Month 1 2, 4, 6 2 3 4 1-12
Thank you very much
April 09, 2007, 05:46 PM
Lusheng
Use Defined fields to define your new fields as concatenated fields based on certain conditons.
April 09, 2007, 05:55 PM
Darin Lee
Here's an example from CAR: DEFINE FILE CAR CONCAT_STR/A200V=IF COUNTRY EQ LAST COUNTRY THEN (CONCAT_STR ||', '|| CAR ) ELSE CAR; END TABLE FILE CAR SUM CONCAT_STR BY COUNTRY END
This assumes that the total length of the concatenated values will never be more than 200, but you can change that based on your need. It also assumes that data is already in order of Sort field (COUNTRY in this case)
You get COUNTRY CONCAT_STR ENGLAND JAGUAR,JENSEN,TRIUMPH FRANCE PEUGEOT ITALY ALFA ROMEO,MASERATI JAPAN DATSUN,TOYOTA W GERMANY AUDI,BMWThis message has been edited. Last edited by: Darin Lee,
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
April 10, 2007, 08:45 AM
Cindy Williams
Here is an example from one of my apps, using a counter. This concatentates a list of up to ten policy riders together, separated by a space. DEFINE FILE HLDRIDER COUNTER/I2=IF POL_NB EQ LAST POL_NB THEN COUNTER + 1 ELSE 1; RDRSTRING/A65=IF POL_NB EQ LAST POL_NB THEN RDRSTRING ELSE ''; RDRSTRING/A65= IF COUNTER EQ 1 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 1, RDRSTRING) ELSE IF COUNTER EQ 2 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 7, RDRSTRING) ELSE IF COUNTER EQ 3 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 13, RDRSTRING) ELSE IF COUNTER EQ 4 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 19, RDRSTRING) ELSE IF COUNTER EQ 5 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 25, RDRSTRING) ELSE IF COUNTER EQ 6 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 31, RDRSTRING) ELSE IF COUNTER EQ 7 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 37, RDRSTRING) ELSE IF COUNTER EQ 8 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 43, RDRSTRING) ELSE IF COUNTER EQ 9 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 49, RDRSTRING) ELSE IF COUNTER EQ 10 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 55, RDRSTRING) ELSE 'MORE'; END TABLE FILE HLDRIDER SUM RDRSTRING BY POL_NB WHERE COUNTER LE 10; ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE HOLD AS HLDRIDER_LTC FORMAT FOCUS INDEX POL_NB
April 10, 2007, 02:42 PM
Danny-SRL
Usernani, Assuming that the field MONTH is I2 and takes values from 1 to 12 and also 99.
DEFINE FILE ...
AMONTH/A4=IF MONTH EQ 99 THEN '1-12' ELSE FTOA(MONTH,'(D4c)','A4');
END
TABLE FILE ...
LIST AMONTH BY KEY
ON TABLE HOLD
END
TABLE FILE HOLD
SUM AMONTH
BY KEY
ACROSS LIST NOPRINT
END
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF