Count, Sum, Divide

[Kashia1122]

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)

[Saravanan_Desingh · Accepted solution]

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;

[Saravanan_Desingh]

In the Chart:

Sum(C2/C1)

[Saravanan_Desingh · Accepted solution]

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

[Kashia1122]

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

[Saravanan_Desingh]

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