topic Re: calculation in script in QlikView

calculation in script

Tue, 13 Nov 2012 10:13:17 GMT

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

swuehl — Tue, 13 Nov 2012 10:17:03 GMT

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

Re: calculation in script

Tue, 13 Nov 2012 10:21:11 GMT

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

swuehl — Tue, 13 Nov 2012 10:53:55 GMT

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

Tue, 13 Nov 2012 10:54:54 GMT

Load

Y3_Autumn_Reading_APS - PupilResultsY2SummerReadingAPS AS FieldName,

Y3_Autumn_Writing_APS - PupilResultsY2SummerWritingAPS as AsYouWant

From

YourFile;

Re: calculation in script

Tue, 13 Nov 2012 10:57:21 GMT

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?

LOAD

Y3_Autumn_Reading_APS - PupilResultsY2SummerReadingAPS ,

Y3_Autumn_Writing_APS - PupilResultsY2SummerWritingAPS as NewField,

...

Re: calculation in script

Tue, 13 Nov 2012 12:16:14 GMT

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?