Merging 4 values and sobstitute with one new one

Question

Hi guys,

I have a problem, I did some interviews (more then 5k people) and I clustered their answers in more then 100 fields with their relative weight. Now I need to substitute 4 of this fields with one new field that has as a weight the avg of the four fields.

The situation As is:

customer 1

filed 1  weight x

.

filed 100  weight x

Custumer 2

field 1           weight x

.

field 100    weight x

.

Customer 5k

What I would like

Customer 1

fiels 1 weight x

.

field 97 weight (avg of 97-98-99-100)

..

Customer 5k

fiels 1 weight x

.

field 97 weight (avg of 97-98-99-100)

Do you think is possible?

Thanks a lot in advance

S

AmeliaG · Answer

Hi @SIMOANTO,

Yes, this is certainly possible. In order to multiply the response by the weight, please make sure the response fields are in a numeric format (such as double). You can manually update data types with the 'Select' tool.

To create a new calculated field, use the 'Formula' tool, if all of the responses for a single user were on the same line.

If the response for each field is on a different line, I would:

1. Use a Join tool to join the weights to the response with the field number as the key field.

2. Use the formula tool to create a new field (i.e. weighted response) which will be equal to weight multiplied by response

3. Use a summarize tool to sum for each user. (Group By: UserID, Sum: Weighted Response)

Does this make sense? If no, please send me sample data and I will build an example.

Thanks!

Amelia