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]

Please check my previous post

[manoranjan_d]

can you please elaborate more because  from the above the table what is the expected the table o/p required

i tried with cross table count to count the exp_1,exp_2,ep_3 for the ptd id

T1:
LOAD id,
Exp_1,
Exp_2,
Exp_3
FROM
[C:\Users\NM\Desktop\Scenarios\21Feb\New Microsoft Excel Worksheet.xlsx]
(ooxml, embedded labels, table is Sheet1);

T2:
CrossTable(EXP,Data,1)
load * Resident T1;
DROP Table T1;