Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ranibosch
Creator
Creator

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?

1 Solution

Accepted Solutions
shanky1907
Creator II
Creator II

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

View solution in original post

16 Replies
mdmukramali
Specialist III
Specialist III

Dear Rani,

Try Group by function:

Value entries:

Load [Document number]

          Sum([Cost amount]) as Cost_Amount

Resident MainTable

Group By [Document number];

ranibosch
Creator
Creator
Author

Hi Mohammed,

thanks for your response.

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

mdmukramali
Specialist III
Specialist III

Hi,

Can you attach sample data file i will make it,

ranibosch
Creator
Creator
Author

Thanks Mohammed.

Please see attached.

shanky1907
Creator II
Creator II

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
Creator
Creator
Author

Hi Shashank,

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

shanky1907
Creator II
Creator II

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
Creator
Creator
Author

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

ranibosch
Creator
Creator
Author

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?