Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

ranibosch
Contributor

Sum a select statement

Hi,

I have the following situation where I've got 2 tables:

     1. GL entries

               Fields:

                         Document number

                         Amount

     2. Value entries

               Fields:

                         Document number

                         Cost amount

My issue is that the cost amount in the value entries can consist out of multiple lines. Thus I need to sum the cost amount for each document number.

I can then work out the GP% for each invoice posted.

how can i group the cost of each document number in the value entries table, and only the value entries table?

Tags (1)
1 Solution

Accepted Solutions
shanky1907
Contributor II

Re: Sum a select statement

Please find the syntax for resident load for your  scenario as below:

**********************this is my general ledger entries i'm loading***********************

GeneralLedgerEntries:

Load

Year("Posting Date") as Year,

Month("Posting Date") as Month,

Day("Posting Date") as Day,

"Document No_" as "Document No",

Amount as "Sales Value",

"Global Dimension 1 Code" as Department,

    "Global Dimension 2 Code" as BU;

   

SQL SELECT

    "G_L Account No_",

    "Posting Date",

    "Document Type",

    "Document No_",

    Amount,

    "Global Dimension 1 Code",

    "Global Dimension 2 Code",

   

   

FROM "DynamicsNAV90_Prod".dbo."Ambassador Foods (Pty) Ltd$G_L Entry"

Where "G_L Account No_" in (1110,1120,1130,1140,1150)

**********************this is my value entries table i'm loading***********************

//VALUE ENTRIES

TempValueEntries:

Load

"Document No_" as "Document No",

"Cost Amount (Actual)";

SQL SELECT

    "Document No_",

    "Cost Amount (Actual)"

      

FROM "DynamicsNAV90_Prod".dbo."Ambassador Foods (Pty) Ltd$Value Entry";

Value Entreis:

Load

[Document No],

sum([Cost Amount (Actual)] AS [Cost Value];

Resident TempValueEntries

Group By [Document No];

Drop table TempValueEntries;

//Store Statement

16 Replies
mdmukramali
Valued Contributor II

Re: Sum a select statement

Dear Rani,

Try Group by function:

Value entries:

Load [Document number]

          Sum([Cost amount]) as Cost_Amount

Resident MainTable

Group By [Document number];

ranibosch
Contributor

Re: Sum a select statement

Hi Mohammed,

thanks for your response.

I'm new to Qlickview and not familiar to RESIDENT MAIN TABLE?

mdmukramali
Valued Contributor II

Re: Sum a select statement

Hi,

Can you attach sample data file i will make it,

ranibosch
Contributor

Re: Sum a select statement

Thanks Mohammed.

Please see attached.

shanky1907
Contributor II

Re: Sum a select statement

Please use the below expression in your Chart/Table/or any other object expression as:

Sum(Aggr(CostAmount, DocumentNumber))

And take DocumentNumber as Dimension in the same Object.

ranibosch
Contributor

Re: Sum a select statement

Hi Shashank,

I would like to sum it in my script before I load it into the model.

shanky1907
Contributor II

Re: Sum a select statement

Please find the syntax for resident load for your  scenario as below:

**********************this is my general ledger entries i'm loading***********************

GeneralLedgerEntries:

Load

Year("Posting Date") as Year,

Month("Posting Date") as Month,

Day("Posting Date") as Day,

"Document No_" as "Document No",

Amount as "Sales Value",

"Global Dimension 1 Code" as Department,

    "Global Dimension 2 Code" as BU;

   

SQL SELECT

    "G_L Account No_",

    "Posting Date",

    "Document Type",

    "Document No_",

    Amount,

    "Global Dimension 1 Code",

    "Global Dimension 2 Code",

   

   

FROM "DynamicsNAV90_Prod".dbo."Ambassador Foods (Pty) Ltd$G_L Entry"

Where "G_L Account No_" in (1110,1120,1130,1140,1150)

**********************this is my value entries table i'm loading***********************

//VALUE ENTRIES

TempValueEntries:

Load

"Document No_" as "Document No",

"Cost Amount (Actual)";

SQL SELECT

    "Document No_",

    "Cost Amount (Actual)"

      

FROM "DynamicsNAV90_Prod".dbo."Ambassador Foods (Pty) Ltd$Value Entry";

Value Entreis:

Load

[Document No],

sum([Cost Amount (Actual)] AS [Cost Value];

Resident TempValueEntries

Group By [Document No];

Drop table TempValueEntries;

//Store Statement

ranibosch
Contributor

Re: Sum a select statement

thank you so much! it is working 100%!!

ranibosch
Contributor

Re: Sum a select statement

another question on this.

I notice now that the document number in my GL entries field must also be aggregated in order get an accurate sales amount.

(same document number has more than 1 line)

how do i get past this now?