topic Re: Count, Sum, Divide in App Development

Count, Sum, Divide

Kashia1122 — Fri, 19 Feb 2021 16:42:00 GMT

Hello,

I hope someone will be able to help me

I have a table with 4 columns.

id -> is the product number; Exp_1, Exp_2, Exp_3 is part of the product.

I need to present data in a table where there will be one column containing data from columns (Exp_1, Exp_2, Exp_3) and number of occurrences of values from column Exp_1, Exp_2, Exp_3

for example, for one id in the Exp_1 column I have the value A and in the Exp_2 column I have the value A. My result for A in the new column should be 1 because I count the amount A in all columns for one id and divide by the number of non-empty columns fields for one id

for example, for an id of 4, C should be 0.5 and A 0.5.

finally I need the sum of the values in the new column (number Exp)

Re: Count, Sum, Divide

Saravanan_Desingh — Fri, 19 Feb 2021 19:49:22 GMT

Try this,

tab1: LOAD * INLINE [ id, Exp1, Exp2, Exp3 1, A, A, 2, B, , 3, A, B, A 4, C, A, 5, A, C, B 6, C, , ]; tab1X: CrossTable(Exp,Value) LOAD * Resident tab1; Left Join(tab1X) LOAD id, Count(If(Len(Value)>0,Value)) As C1 Resident tab1X Group By id; Left Join(tab1X) LOAD id, Value, Count(If(Len(Value)>0,Value)) As C2 Resident tab1X Group By id, Value; Drop Table tab1;

Re: Count, Sum, Divide

Saravanan_Desingh — Fri, 19 Feb 2021 19:49:58 GMT

In the Chart:

Sum(C2/C1)

Re: Count, Sum, Divide

Saravanan_Desingh — Sat, 20 Feb 2021 15:47:16 GMT

I think, I should use AGGR here. But please confirm the sum for A.

Re: Count, Sum, Divide

Kashia1122 — Sun, 21 Feb 2021 18:59:30 GMT

Thank you Saran7de 🙂 it is working perfectly. This is what I needed. I used AGGR.

Re: Count, Sum, Divide

Saravanan_Desingh — Mon, 22 Feb 2021 00:07:29 GMT

If it answered, please close the thread by selecting the solution