Hello All,

I have a dataset like below:

c1 c2 c3
------------
A1 001 1
A1 002 2
A1 003 1
A2 101 1
A2 102 2
A2 103 3

I need to display count.dst of column c3 (BY C1)

c1 c2 Count.dstC3
--------------------
A1 001 2
A1 002 0
A1 003 0
A2 101 3
A2 102 0
A2 103 0

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 RJ

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

Not clear on what you exactly need.

TABLE FILE CAR
SUM
CNT.CAR
CNT.DST.DCOST
CNT.RCOST
BY COUNTRY
END

This works fine.

Hello Ram,

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.

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

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

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.

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,

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

Thanks ALL,
the above suggestion from Dan helped.

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