C3 is of type integer(I4). Is there any way to eliminate the 0's from the count column and replace it with blank. I have also tried changing the datatye to alpha(A4) but that appends 0's in the front of the count values (0002). Any suggestions:
Thnks RJThis message has been edited. Last edited by: Kerry,
7.1.4 Windows XP Excel, HTML, PDF
April 27, 2010, 04:18 AM
Ram Prasad E
Not clear on what you exactly need.
TABLE FILE CAR
SUM
CNT.CAR
CNT.DST.DCOST
CNT.RCOST
BY COUNTRY
END
Thnks for replying, but i need a print for the 1st 2 fields. All the fields except c3 should be as it is. C3 should have a count.c3.
7.1.4 Windows XP Excel, HTML, PDF
April 27, 2010, 04:51 AM
shakila25
Hi Ranjana, Please find below code. I don't know the below code will match ur requirement.
quote:
TABLE FILE CAR SUM CNT.RCOST PRINT CAR DCOST BY COUNTRY END
WebFOCUS 7.6.x Windows Output: Excel,PDF, HTML
April 27, 2010, 04:57 AM
Ram Prasad E
Are you looking something like this?
SET HOLDLIST = PRINTONLY
TABLE FILE CAR
SUM
CNT.DST.SEATS
BY COUNTRY
ON TABLE HOLD AS H1
END
-*?FF H1
-*-EXIT
TABLE FILE CAR
BY COUNTRY
BY CAR
BY SEATS
ON TABLE HOLD AS H2
END
JOIN COUNTRY IN H1 TO MULTIPLE COUNTRY IN H2
-RUN
TABLE FILE H1
PRINT
COMPUTE CS/I4=IF COUNTRY NE LAST COUNTRY THEN H1.SEATS ELSE 0; AS 'Count Seats'
BY H1.COUNTRY AS 'Country'
BY H2.CAR AS 'Car'
BY H2.SEATS AS 'Seats'
BY H1.SEATS NOPRINT
END
This message has been edited. Last edited by: Ram Prasad E,
@Shakila: i shall try ur suggestion and let u kno, Thanks
@Ram: I did try the same, but it gives me the 0's which i want to get rid off
Thanks
7.1.4 Windows XP Excel, HTML, PDF
April 27, 2010, 07:08 AM
shakila25
Ranjana, COMPUTE CS/I4 MISSING ON =IF COUNTRY NE LAST COUNTRY THEN H1.SEATS ELSE MISSING; AS 'Count Seats' Please try the above line in Ram's code.
WebFOCUS 7.6.x Windows Output: Excel,PDF, HTML
April 27, 2010, 06:32 PM
Dan Satchell
If you use the MISSING=ON method suggested above, you will need to add
ON TABLE SET NODATA ' '
to your final TABLE FILE to display the null values as blanks. The drawback is that this will affect all null values in all columns. An alternative is to format only your COMPUTEd column to suppress zeroes. The S in the format (I5S) in the code below replaces zeroes with blanks. Also, if your original table is sorted and/or keyed on column 1 (C1), you don't need the second hold file. You should be able to simply join back to the original table.
TABLE FILE CAR
COUNT DST.SEATS AS 'CNT_DST'
BY COUNTRY
ON TABLE HOLD AS H1
END
-*
JOIN CLEAR *
JOIN COUNTRY IN H1 TO ALL COUNTRY IN CAR AS J1
-*
TABLE FILE H1
PRINT
COMPUTE CNT_SEATS/I5S = IF COUNTRY NE LAST COUNTRY THEN H1.CNT_DST ELSE 0 ; AS 'C3'
BY H1.COUNTRY AS 'C1'
BY CAR.CAR AS 'C2'
END
This message has been edited. Last edited by: Dan Satchell,
WebFOCUS 7.7.05
April 28, 2010, 12:38 AM
Dan Satchell
On the other hand, sometimes suppressing all zeroes in a column is not desirable because some of those zeroes may be valid. In that case, the MISSING=ON/SET NODATA method will allow you to display valid zeroes while suppressing others with blanks. For example, in the sample code below if the value of CNT_SEATS were zero for a particular value of C1, the zero value would display as zero for the first C1 row while other rows with the same C1 value would display as blanks.
TABLE FILE CAR
COUNT DST.SEATS AS 'CNT_DST'
BY COUNTRY
ON TABLE HOLD AS H1
END
-*
JOIN CLEAR *
JOIN COUNTRY IN H1 TO ALL COUNTRY IN CAR AS J1
-*
TABLE FILE H1
PRINT
COMPUTE CNT_SEATS/I5 MISSING ON = IF COUNTRY NE LAST COUNTRY THEN H1.CNT_DST ELSE MISSING ; AS 'C3'
BY H1.COUNTRY AS 'C1'
BY CAR.CAR AS 'C2'
ON TABLE SET NODATA ' '
END
WebFOCUS 7.7.05
April 28, 2010, 01:37 AM
Ranjana
Thanks ALL, the above suggestion from Dan helped.
7.1.4 Windows XP Excel, HTML, PDF
April 28, 2010, 03:20 AM
Dan Satchell
But wait ... there's more. Just to prove that where there's a will there is almost always a way in WebFOCUS - by using the reformatting option when displaying columns, you can selectively format column C3 as either 'I5' or 'I5S':
TABLE FILE CAR
COUNT DST.SEATS AS 'CNT_DST'
BY COUNTRY
ON TABLE HOLD AS H1
END
-*
JOIN CLEAR *
JOIN COUNTRY IN H1 TO MULTIPLE COUNTRY IN CAR AS J1
-*
TABLE FILE H1
PRINT
COMPUTE C3FORMAT/A8 = IF COUNTRY NE LAST COUNTRY THEN 'I5' ELSE 'I5S' ; NOPRINT
COMPUTE CNT_SEATS/I5 = IF COUNTRY NE LAST COUNTRY THEN H1.CNT_DST ELSE 0 ; NOPRINT
CNT_SEATS/C3FORMAT AS 'C3'
BY H1.COUNTRY AS 'C1'
BY CAR.CAR AS 'C2'
END