1 Reply Latest reply: May 16, 2016 5:52 AM by Gysbert Wassenaar RSS

    Help in finding out formula!

    Kshitija Shinde

      Hello everyone,

       

                Finding difficulty in calculating formula:

       

      I Have four tables and their fields as metioned below:

      1) MISGrpMaster:

      • MISGrpID,
      • MISGrpName,
      • DispOrder

      2) MISSubGrpMaster:

      • MISSubGrpID,
      • MISGrpID,
      • MISSubGrpName,
      • DispOrder

      3)MISParticularsMaster:

      • MisParticularsID,
      • MisSubGrpID,
      • SunAcctCode,
      • MisParticularDetail

      4)MISBasicTransaction:

      • TransID,
      • SunAcctCode,
      • AccountingPeriod,
      • TransactionAmt

       

      And here is my code as follows:

      LIB CONNECT TO 'TempBI ()';

       

      LOAD MISGrpID,

          MISGrpName,

          DispOrder as GrpDispOrder;

      SQL SELECT MISGrpID,

          MISGrpName,

          DispOrder

      FROM "WSG_BI".dbo.MISGrpMaster;

       

      LIB CONNECT TO 'TempBI ()';

       

      LOAD MISSubGrpID,

          MISGrpID,

          MISSubGrpName,

          DispOrder as SubGrpDispOrder;

      SQL SELECT MISSubGrpID,

          MISGrpID,

          MISSubGrpName,

          DispOrder

      FROM "WSG_BI".dbo.MISSubGrpMaster;

       

       

      LIB CONNECT TO 'TempBI ()';

       

      LOAD MisParticularsID,

          MisSubGrpID,

          SunAcctCode,

          MisParticularDetail;

      SQL SELECT MisParticularsID,

          MisSubGrpID,

          SunAcctCode,

          MisParticularDetail

      FROM "WSG_BI".dbo.MISParticularsMaster;

       

      LIB CONNECT TO 'TempBI ()';

       

      LOAD TransID,

          SunAcctCode,

          AccountingPeriod,

          left(AccountingPeriod,4) as APYear,

          right(AccountingPeriod,3) as APMonthId,

          TransactionAmt;

      SQL SELECT TransID,

          SunAcctCode,

          AccountingPeriod,

          TransactionAmt

      FROM "WSG_BI".dbo.MISBasicTransaction;

       

      Month_Name:

      Load  *  inline [ APMonthId,APMonthName,Monthorder

        001,Apr,1

                  002,May,2

                  003,Jun,3

                  004,Jul,4

                  005,Aug,5

                  006,Sep,6

                  007,Oct,7

                  008,Nov,8

                  009,Dec,9

                  010,Jan,10

                  011,Feb,11

                  012,Mar,12

            ];

       

       

      SO MY QUESTION IS THAT I WANT TO CALCULATE SUM OF TRANSACTION OF THE BELOW ATTACHED IMAGE FIELDS:

      op.png

      SUM OF TRANSACTION OF ALL THE MISSubGrpName fields where MISSubGrpName is from MISGrpName...

      I hope you 'll understand my question..I wanna know how to calculate this query

      Thanks in advance.

      Regards