Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello gentlemen,
I'm stuck with a problem. I have a data set like this example:
PersonID | Score_Jan | Score_Feb | Score_Mar |
10007 | 1 | 2 | 1 |
10008 | 2 | 3 | 4 |
10009 | 2 | 3 | 1 |
I need to add a column with the value for the standard deviation for all the scores for each Person ID. Do you guys know how to do it?
Assuming you have a fixed number of fields, use RangeStdev function.
-Rob
Assuming you have a fixed number of fields, use RangeStdev function.
-Rob
Thanks a lot! I didn't know this function. Tbh, my data have a dynamic number of fields, but it's easy to solve with variables. I did something like:
Test:
LOAD * INLINE [
id, test1, test2, test3
1, 3, 2, 1
2, 2, 2, 1
3, 1,, 2
4, 2,, 3
5, 1, 1, 1
6, 2, 2, 2
7, 1, 2, 3
];
LET VNo.ofFields = NOOFFIELDS('Test'); // To find No.of Fields in the table
FOR i=2 TO $(VNo.ofFields)
LET VFieldName = FIELDNAME($(i),'Test'); // To find FieldName in the ith position.
FieldNames:
LOAD
$(i) AS ID,
'$(VFieldName)' AS Field
AUTOGENERATE 1;
NEXT i;
Fields:
LOAD
CONCAT(Field,', ', ID) AS Fields
RESIDENT FieldNames;
LET vFieldNames = PEEK('Fields', 0,'Fields');
DROP TABLES Fields, FieldNames;
SD_test:
LOAD
REP,
RangeStDev($(vFieldNames))
RESIDENT Test;