6 Replies Latest reply: Nov 13, 2012 7:16 AM by Tim Hop RSS

    calculation in script

      Hi all,

      I am trying to do a cakculation in the script and give it a new field name but am unsure on how to do it.

      Here is what i am trying to do

       

      I have lots of fields that i want to subtract one from the other.

       

      Y3_Autumn_Reading_APS - PupilResultsY2SummerReadingAPS

      Y3_Autumn_Writing_APS - PupilResultsY2SummerWritingAPS

      Y3_Autumn_Mathematics_APS - PupilResultsY2SummerMathematicsAPS

      Y3_Autumn_ICT_APS - PupilResultsY2SummerICTAPS

      Y3_Autumn_Science_APS - PupilResultsY2SummerScienceAPS

      Y4_Autumn_Reading_APS - PupilResultsY2SummerReadingAPS

      Y4_Autumn_Writing_APS - PupilResultsY2SummerWritingAPS

      Y4_Autumn_Mathematics_APS - PupilResultsY2SummerMathematicsAPS

      Y4_Autumn_ICT_APS - PupilResultsY2SummerICTAPS

      Y4_Autumn_Science_APS - PupilResultsY2SummerScienceAPS

      Y5_Autumn_Reading_APS - PupilResultsY2SummerReadingAPS

      Y5_Autumn_Writing_APS - PupilResultsY2SummerWritingAPS

      Y5_Autumn_Mathematics_APS - PupilResultsY2SummerMathematicsAPS

      Y5_Autumn_ICT_APS - PupilResultsY2SummerICTAPS

      Y5_Autumn_Science_APS - PupilResultsY2SummerScienceAPS

      Y6_Autumn_Reading_APS - PupilResultsY2SummerReadingAPS

      Y6_Autumn_Writing_APS - PupilResultsY2SummerWritingAPS

      Y6_Autumn_Mathematics_APS - PupilResultsY2SummerMathematicsAPS

      Y6_Autumn_ICT_APS - PupilResultsY2SummerICTAPS

      Y6_Autumn_Science_APS - PupilResultsY2SummerScienceAPS

      Y3_Summer_Reading_APS - PupilResultsY2SummerReadingAPS

      Y3_Summer_Writing_APS - PupilResultsY2SummerWritingAPS

      Y3_Summer_Mathematics_APS - PupilResultsY2SummerMathematicsAPS

      Y3_Summer_ICT_APS - PupilResultsY2SummerICTAPS

      Y3_Summer_Science_APS - PupilResultsY2SummerScienceAPS

      Y4_Summer_Reading_APS - PupilResultsY2SummerReadingAPS

      Y4_Summer_Writing_APS - PupilResultsY2SummerWritingAPS

      Y4_Summer_Mathematics_APS - PupilResultsY2SummerMathematicsAPS

      Y4_Summer_ICT_APS - PupilResultsY2SummerICTAPS

      Y4_Summer_Science_APS - PupilResultsY2SummerScienceAPS

      Y5_Summer_Reading_APS - PupilResultsY2SummerReadingAPS

      Y5_Summer_Writing_APS - PupilResultsY2SummerWritingAPS

      Y5_Summer_Mathematics_APS - PupilResultsY2SummerMathematicsAPS

      Y5_Summer_ICT_APS - PupilResultsY2SummerICTAPS

      Y5_Summer_Science_APS - PupilResultsY2SummerScienceAPS

      Y6_Summer_Reading_APS - PupilResultsY2SummerReadingAPS

      Y6_Summer_Writing_APS - PupilResultsY2SummerWritingAPS

      Y6_Summer_Mathematics_APS - PupilResultsY2SummerMathematicsAPS

      Y6_Summer_ICT_APS - PupilResultsY2SummerICTAPS

      Y6_Summer_Science_APS - PupilResultsY2SummerScienceAPS

       

      AS NewField

       

      Is this possible?

        • Re: calculation in script
          Stefan Wühl

          Are all these fields located in one table? Do you want to add up each line or ...?

            • Re: calculation in script

              Hi, yes they are all in the same table. i want to calculate line by line. this data is related to schools, so each student will have a figure against each of the above fields. i want to keep it that way so i can get a figure for each student. if that makes sense. ?

               

              .So student A

              Y6_Summer_ICT_APS - PupilResultsY2SummerICTAPS

               

              If Y6_Summer_ICT_APS has a figure for the student of 25

              and

              PupilResultsY2SummerICTAPS has a figure of 15

              then i would want a new figure of 10 for this student (25-10)

               

              Does that make sense?

                • Re: calculation in script
                  Stefan Wühl

                  Not sure if I understand, you want something like

                   

                  LOAD

                  Y3_Autumn_Reading_APS - PupilResultsY2SummerReadingAPS as ReadingResult,

                  Y3_Autumn_Writing_APS - PupilResultsY2SummerWritingAPS as WritingResult,

                  ...

                  FROM ...;

                   

                  ??

                    • Re: calculation in script

                      Hi, no, i wanted to do all the calculations then save it as ONE new fieldname rather than several. but i am not sure if that is possible?

                       

                      so

                       

                      LOAD

                      Y3_Autumn_Reading_APS - PupilResultsY2SummerReadingAPS ,

                      Y3_Autumn_Writing_APS - PupilResultsY2SummerWritingAPS as NewField,

                      ...

                    • Re: calculation in script

                      Load

                      Y3_Autumn_Reading_APS - PupilResultsY2SummerReadingAPS AS FieldName,

                      Y3_Autumn_Writing_APS - PupilResultsY2SummerWritingAPS as  AsYouWant

                      From

                      YourFile;

                        • Re: calculation in script

                          Hi, ok i have done sort of what i wanted. i have created new fields per subject / term as below

                           

                          LOAD PupilID,

                               if(PupilYearGroup=3,Y3_Autumn_Reading_APS-PupilResultsY2SummerReadingAPS,

                                 if(PupilYearGroup=4,Y4_Autumn_Reading_APS-PupilResultsY2SummerReadingAPS,

                                   if(PupilYearGroup=5,Y5_Autumn_Reading_APS-PupilResultsY2SummerReadingAPS,

                                     if(PupilYearGroup=6,Y6_Autumn_Reading_APS-PupilResultsY2SummerReadingAPS,

                                 ))))                                                                            as AutumnReadingProgress,

                               if(PupilYearGroup=3,Y3_Spring_Reading_APS-PupilResultsY2SummerReadingAPS,

                                 if(PupilYearGroup=4,Y4_Spring_Reading_APS-PupilResultsY2SummerReadingAPS,

                                   if(PupilYearGroup=5,Y5_Spring_Reading_APS-PupilResultsY2SummerReadingAPS,

                                     if(PupilYearGroup=6,Y6_Spring_Reading_APS-PupilResultsY2SummerReadingAPS,

                                 ))))                                                                             as SpringReadingProgress

                           

                           

                          Now i want to create a chart with an expression to do the following..

                           

                          if pupilyeargroup ='4' and term = 'Autumn' then display a count of pupilID that have a  AutumnReadingProgress score of 4 or more.

                          if pupilyeargroup ='5' and term = 'Spring' then display a count of pupilID that have a  AutumnReadingProgress score of 8 or more etc.

                           

                          can anyone help with writing this please?