Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Karahs
Partner - Creator
Partner - Creator

Help in finding out formula!

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

1 Reply
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Simply select a MISGrpName value and calculate the results with sum(TransactionAmt)


talk is cheap, supply exceeds demand