Hi Team,

I have a data like below:
TGT_CNDT_I
----------
465779
465779
465780
465780
465781
0
465765
465765

I am doing a DST.CNT.TGT_CNDT_I to get the Distinct count.
Expected Value: 4
Actual Value: 5 (Because of zero in the data).

is there any way to exclude the zero while caluculating this Distinct Count???

Any help on this appreciated.

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

Have you tried this ?

WHERE TGT_CNDT_I GT 0

  

TGT_CNDT_I	TGT_APPL_I	TGT_CNCT_I	TGT_INT_I_1	TGT_INT_I_2	TGT_OFR_I
465779	1	1	1	1	1
465779	1	1	1	0	0
465780	1	1	1	1	1
465780	0	0	0	0	0
465781	0	0	0	0	0
0	1	1	1	1	1
465765	1	1	1	1	1
465765	1	0	0	0	0

My Data is will be like above.
And I am doing SUM on all these filelds.
But for the Field TGT_CNDT_I i need a Distinct Count Value.
If I use the "WHERE TGT_CNDT_I GE 0", i will loose other Fields data while counting.

Any Suggessions Forum Friends..???

Thanks.

Harikrishna,


Can you please posr your code.

Hello ,

Thaanks for validating my Data. Please find my WebFOCUS code below, hope you can understand easily:

  
TABLE FILE TGT_T_SRCR_S
PRINT
TGT_SRCR_MGR_N
TGT_SRCR_N
TGT_RPT_DET_2_T
TGT_RPT_DET_3_T
TGT_RPT_DET_4_T
TGT_RPT_DET_5_T
TGT_SRCR_LEAD_Q

COMPUTE V_TGT_SRCR_LEAD_Q/I04 = IF (TGT_SRCR_LEAD_D GE '&ST_DT' AND TGT_SRCR_LEAD_D LE '&END_DT') THEN TGT_SRCR_LEAD_Q ELSE 0 ;

COMPUTE V_TGT_CNDT_I/P20 = IF (TGT_SRCR_CNTC_D GE '&ST_DT' AND TGT_SRCR_CNTC_D LE '&END_DT') THEN TGT_CNDT_I ELSE 0 ;

COMPUTE V_TGT_SRCR_CNTC_Q/I04 = IF (TGT_SRCR_CNTC_D GE '&ST_DT' AND TGT_SRCR_CNTC_D LE '&END_DT') THEN TGT_SRCR_CNTC_Q ELSE 0 ;

COMPUTE V_TGT_SRCR_APPL_Q/I04 = IF (TGT_SRCR_APPL_D GE '&ST_DT' AND TGT_SRCR_APPL_D LE '&END_DT') THEN TGT_SRCR_APPL_Q ELSE 0 ;

COMPUTE V_TGT_INIL_INTVW_Q/I04 = IF (TGT_INIL_INTVW_D GE '&ST_DT' AND TGT_INIL_INTVW_D LE '&END_DT') THEN TGT_INIL_INTVW_Q ELSE 0 ;

COMPUTE V_TGT_FINL_INTVW_Q/I04 = IF (TGT_FINL_INTVW_D GE '&ST_DT' AND TGT_FINL_INTVW_D LE '&END_DT') THEN TGT_FINL_INTVW_Q ELSE 0 ;

COMPUTE V_TGT_OFR_EXT_Q/I04 = IF (TGT_OFR_EXT_D GE '&ST_DT' AND TGT_OFR_EXT_D LE '&END_DT') THEN TGT_OFR_EXT_Q ELSE 0 ;

COMPUTE V_TGT_OFR_ACPT_Q/I04 = IF (TGT_OFR_ACPT_D GE '&ST_DT' AND TGT_OFR_ACPT_D LE '&END_DT') THEN TGT_OFR_ACPT_Q ELSE 0 ;

COMPUTE V_TGT_ACTL_STRT_Q/I04 = IF (TGT_ACTL_STRT_D GE '&ST_DT' AND TGT_ACTL_STRT_D LE '&END_DT') THEN TGT_ACTL_STRT_Q ELSE 0 ;
WHERE TGT_RPT_DET_2_T EQ 'Distribution Group 4' OR 'Distribution Group 3'
WHERE TGT_SRCR_MGR_N EQ 'Yoder,Larry'
WHERE TGT_SRCR_N EQ 'Abbott,Erin' OR 'Knapp,Kevin'
ON TABLE HOLD AS SRCR_S_H
END

-* From the above created hold file Counting the values using the below code. 
-* TGT_SRCR_LEAD_Q,TGT_SRCR_CNTC_Q,TGT_SRCR_APPL_Q,TGT_INIL_INTVW_Q,TGT_FINL_INTVW_Q,TGT_OFR_EXT_Q,TGT_OFR_ACPT_Q,TGT_OFR_ACPT_Q contains 1/0 in Database.
-* TGT_CNDT_I  this contains Ids like 34566, 34566, 34678 (Have duplicate values as well in Database.)
-* So while counting this particular field i am using "CNT.DST.V_TGT_CNDT_I", but because of "0" i am getting 1 count extra in Results.

TABLE FILE SRCR_S_H
SUM
V_TGT_SRCR_LEAD_Q AS 'Lds'
CNT.DST.V_TGT_CNDT_I AS 'Dsnt Cnts'
V_TGT_SRCR_CNTC_Q AS 'All Cnts'
V_TGT_SRCR_APPL_Q AS 'Applications'
V_TGT_INIL_INTVW_Q AS 'Initial Interview'
V_TGT_FINL_INTVW_Q AS 'Interviews'
COMPUTE SRC_OFR_RT/I04% = (V_TGT_OFR_EXT_Q/V_TGT_SRCR_CNTC_Q) * 100 ; AS 'Source Offer Rate'
V_TGT_OFR_EXT_Q AS 'Source Offers'
COMPUTE SRC_ACPT_RT/I04% = (V_TGT_OFR_ACPT_Q/V_TGT_SRCR_CNTC_Q) * 100 ; AS 'Source Accept Rate'
V_TGT_OFR_ACPT_Q AS 'Source Accepts'
V_TGT_ACTL_STRT_Q AS 'Source Starts'
BY TGT_SRCR_MGR_N AS 'Sourcer TA Manager'
BY TGT_SRCR_N AS 'Sourcer'
BY TGT_RPT_DET_2_T AS 'HQ Pyramid/DC Group/STR Region'
BY TGT_RPT_DET_3_T AS 'HQ Business Unit/Store Group/DC Location'
BY TGT_RPT_DET_4_T AS 'Req Staffing Type'
BY TGT_RPT_DET_5_T AS 'Job Type/Job Group/Emp Class'
ON TGT_SRCR_N RECOMPUTE AS 'Sub Total'
ON TGT_SRCR_MGR_N RECOMPUTE AS 'Total for'

Thanks,

TABLE FILE WHATEVER
SUM
COMPUTE UNIQUE_CNDT/I1=IF TGT_CNDT_I EQ '0' THEN 1 ELSE 0;
BY TGT_CNDT_I
PRINT TGT_CNDT_I TGT_APPL_I etc.
BY TGT_CNDT_I
ON TABLE COLUMN-TOTAL 
END

You will get a 1 in column 2 for every unique number (excluding the record with the zero) which will total at the bottom. Of course your records are going to be sorted (with all the zero records a the top)and maybe you don't want to see the first two columns. That might mean using LIST and a hold file to sequence the records initially so you can put them back in the correct order for the final output.

George

Thnaks George,

This code is helpful, and my count issue is fixed. I am in a validation now. Will share the latest code once i complete the Validation.

Thanks.