2 Replies Latest reply: Sep 13, 2014 3:45 AM by Manish Kachhia RSS

    Problem with script level calculations

      Hello Everyone,

       

      I am trying to do some script level calculations, but i am getting error, Please check the below script.

       

         Load Channel_Name,

                Revnue_SP,

                Revnue_SP,

                Room_Nights_CS,

                Revenue_CS,

           ((sum(Revnue_SP)/sum(Revenue_CS))*100) as [MySource_RevPAR_Index],

           (sum(Revnue_SP)/sum(NO_OF_ROOMS)) as [MySource_Occ_Prop],

           (sum(Room_Nights_CS)/sum(AVAIL_ROOMS)) as [MySource_Occ_CS],

           ((sum(MySource_Occ_Prop)/sum(MySource_Occ_CS))*100) as [MySource_Occ_Index],

           (sum(Revnue_SP)/sum(Revnue_SP)) as [MySource_ADR_Prop],

           (sum(Revenue_CS)/sum(Room_Nights_CS)) as [MySource_ADR_CS],

           ((sum(MySource_ADR_Prop)/sum(MySource_ADR_CS))*100) as [MySource_ADR_Index],

       

       

      from Mysource(qvd);

       

      When i am trying to run this script the script is stopped working.

       

      Note: here i took the NO_OF_ROOMS and AVAIL_ROOMS from other dimension table which are available in other tabs in my script

       

      Please Help into this

       

      Thanks

        • Re: Problem with script level calculations
          Harshal Patil

          Hi Sreeharsha,

           

          You need to do group by when you are performing aggregation function like sum...

           

          from Mysource(qvd) group by YourDimensionsepratedbycomma;


          Also when you load this table all calculation fields need to be present from same table otherwise if you are taking some fields from different table it will give error .For this case you need to concatenate your another dimension table with sourcetable qvd or depending on data perform some join to do calculation of your measures.


          Like this,


          Basetbl:

          a,

          b,

          c

          from source.qvd;


          concatenate(Basetbl)


          //AnotherDimTbl:

          e,

          f,

          g

          from DimTbl;


          ResultTbl:

          load *,

          a/e as ans1,

          c/f as ans2

          resident Basetbl group by a,b,c,e,f,g;

           

          Hope it will match with yur query..

          • Re: Problem with script level calculations
            Manish Kachhia

            1) Load data from your QVD

            2) use Resident Load with Group By and Join with the Original Data...

             

            You can upload sample data file to get help better and faster way... It is difficult to work without any data or without looking your apps...

             

            Thanks

            Manish