Distinct count based on result of sum calculations

[JustinM]

Hi

I am trying to calculate a distinct count based on the result of a sum calculation on various items (only distinct count this ids where the sum of all tx in a CovPeriod do not equal 0):

CovPeriod	Riskid	Tx	Value
1	1	2	500
1	2	2	100
1	2	7	-100
1	3	2	200
1	3	7	-50
2	1	2	500
2	3	2	200
2	3	7	-200

Expected Answer

CovPeriod	Distinct Count
1	2      (Effectively id 1 & 3) (ID 2 sums to zero)
2	1       (Effectivly Id 1 & 3)
33

I tried:  Count({<Sum(TOTAL <riskid,CovPer>Value)<>{'0'}>}Distinct [riskid]), but it threw out an expression error?

Any advice on where I'm going wrong or overcomplicating things would be much appreciated

[Saravanan_Desingh]

Try this,

tab1:
LOAD * INLINE [
    CovPeriod, Riskid, Tx, Value
    1, 1, 2, 500
    1, 2, 2, 100
    1, 2, 7, -100
    1, 3, 2, 200
    1, 3, 7, -50
    2, 1, 2, 500
    2, 3, 2, 200
    2, 3, 7, -200
];

Left Join(tab1)
LOAD CovPeriod, Riskid, Sum(Value) As CR_Value
Resident tab1
Group By CovPeriod, Riskid;

[Saravanan_Desingh]

Output:

[JustinM]

Thanks Saran

Please bear with me as I'm new at this. Appreciate your patience

1) Do I just enter the above solution as is into the expression or does it need to go into load script?

2)How do I capture the data part (1,1,2,500) etc as my data has 4m+ lines

Thanks