Let me try to explaine my issue. I want to create a fields in a table file or a define and for each row i want to concatenate in the field the previous value with the current value
For example here is the value I seprate the column by # #1#Ludo #2#Dr Nick #3#Toto
and the result should be 1 Ludo Ludo 2 Dr Nick Ludo Dr Nick 3 Toto Ludo Dr Nick Toto
HOw could I perform a such concatenation
I tried
TABLE FILE TEST
PRINT
FIELD1
COMPUTE RESULT/A200 = IF (RESULT EQ LAST RESULT) THEN FIELD1 ELSE RESULT | FIELD1
END
Thx
January 17, 2008, 12:04 PM
GinnyJakes
I know what your problem is but have not thought of a good solution yet. Your problem with doing the concatenation to the LAST field into the same field is that you will get an error because your result of the left-hand side of the equal is too small. So if RESULT is 200 and FIELD1 is 10, then the field you concat into has to be at least 210.
Hmmmm.....
I'm sure someone will think of something. I'll keep trying.
The solution you mentioned in the original post would work if you didn't have the problem with the field size that Ginny mentioned.
The way to solve that issue is to use SUBSTR. Think of it this way: If the final size is 200 and a will do this for 20 records, each adding 10 characters, then I can take 10 characters then first time, 20 the second, etc.
Technically the SUBSTR uses 0 characters the first time and 10 the next since its always a record behind. Using concatenation with the current record gives you what you want. By the way, your if logic changes it back to 10, 20, etc.
Fernando
Prod WF 8.1.04, QA WF 8.2.03, Dev WF 8.2.03
January 17, 2008, 12:59 PM
Alan B
Try using an AnV field:
COMPUTE RESULT/A200V = IF (RESULT EQ LAST RESULT) THEN FIELD1 ELSE RESULT | FIELD1
This should work if I remember correctly.
Alan. WF 7.705/8.007
January 17, 2008, 12:59 PM
Tony A
Ludo,
I got this to work in 7.1.3 -
TABLE FILE CAR SUM COMPUTE LST_CTRY/A200V = LAST COUNTRY || (' ' | LST_CTRY); COMPUTE THIS_CTRY/A200V = COUNTRY || (' ' | LST_CTRY); BY COUNTRY ON TABLE SET HTMLCSS ON ON TABLE SET PAGE NOLEAD END
and here is the output -
COUNTRY
LST_CTRY
THIS_CTRY
ENGLAND
ENGLAND
FRANCE
ENGLAND
FRANCE ENGLAND
ITALY
FRANCE ENGLAND
ITALY FRANCE ENGLAND
JAPAN
ITALY FRANCE ENGLAND
JAPAN ITALY FRANCE ENGLAND
W GERMANY
JAPAN ITALY FRANCE ENGLAND
W GERMANY JAPAN ITALY FRANCE ENGLAND
T
Finally got the output showing (ish) and I see Alan had the same idea at the same time .....This message has been edited. Last edited by: Tony A,
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
January 18, 2008, 02:59 AM
<ludo>
Thx a lot guys it works great.
In fact I used the A200V and in my previous sample I made a mistake that I changed .
the code is
TABLE FILE TEST
PRINT
FIELD1
COMPUTE RESULT/A1000V = IF (RESULT EQ LAST RESULT) THEN RESULT | FIELD1 ELSE FIELD1;
END
May 22, 2008, 10:38 AM
Laurie
How can I get this result
COUNTRY CAR ENGLAND JAGUAR, JENSEN, TRIUMPH FRANCE PEUGEOT ITALY ALFA ROMEO, MASERATI JAPAN DATSUN, TOYOTA
from this: COUNTRY CAR ENGLAND JAGUAR JENSEN TRIUMPH FRANCE PEUGEOT ITALY ALFA ROMEO MASERATI JAPAN DATSUN TOYOTA
May 22, 2008, 10:58 AM
Francis Mariani
I can get it to work with two passes (this may be because the CAR db is hierarchical, or because the data needs to be previously sorted in the correct order):
TABLE FILE CAR
SUM
CAR NOPRINT
BY COUNTRY
BY CAR
ON TABLE HOLD AS H001
END
DEFINE FILE H001
CARS1/A200V = IF COUNTRY EQ LAST COUNTRY THEN CARS || (' ' | CAR) ELSE CAR;
END
TABLE FILE H001
SUM
CARS1
BY COUNTRY
END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
May 22, 2008, 11:12 AM
Prarie
You'll need to throw the , in there (', ' | CAR)
In Focus since 1993. WebFOCUS 7.7.03 Win 2003
May 22, 2008, 11:17 AM
Tony A
Laurie,
You only have to extrapolate the solution from the previous example -
APP PREPENDPATH IBISAMP
TABLE FILE CAR
SUM COMPUTE LST_CAR/A200V = IF COUNTRY EQ LAST COUNTRY THEN LAST CAR || (', ' | LST_CAR) ELSE ''; NOPRINT
COMPUTE THIS_CAR/A200V = CAR || (', ' | LST_CAR);
BY COUNTRY
BY CAR NOPRINT
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
END
As per Francis, to get the final result you will need to hold the output and then get the last value by doing SUM THIS_CAR BY COUNTRY
TThis message has been edited. Last edited by: Tony A,
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
May 22, 2008, 11:23 AM
GinnyJakes
I did this in one pass.
TABLE FILE CAR
SUM
COMPUTE CARCNT/I4=IF COUNTRY NE LAST COUNTRY THEN 1 ELSE CARCNT+1; NOPRINT
COMPUTE CARS1/A200V = IF COUNTRY EQ LAST COUNTRY THEN CARS1 || (', ' | CAR) ELSE CAR;
BY COUNTRY
BY HIGHEST 1 CARCNT NOPRINT
BY CAR NOPRINT
END