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?

16 Replies
shanky1907
Creator II
Creator II

Yes, you can do that. Same way..

ranibosch
Creator
Creator
Author

can one have a where statement in a resident load?

mdmukramali
Specialist III
Specialist III

Hi,

Yes you can write where condition in Resident Load.

ranibosch
Creator
Creator
Author

what is wrong with this script?

it seems not to be working...

mdmukramali
Specialist III
Specialist III

//GENERAL LEDGER ENTRIES

TempGeneralLedgerEntries:

Load

"G_L Account No_",

Year("Posting Date") as Year,

Month("Posting Date") as Month,

Day("Posting Date") as Day,

"Global Dimension 1 Code" as Department,

    "Global Dimension 2 Code" as BU,

    "Document No_" as "Document No",

    Amount as "Sales Amount";

SQL SELECT

"G_L Account No_",

    "Posting Date",

    "Global Dimension 1 Code",

    "Global Dimension 2 Code",

    "Document No_",

    Amount

  

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

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

//*****************sum of sales amounts****************************

GeneralLedgerEntries:

Load

"G_L Account No_",

Year,

Month,

Day,

Department,

BU,

    "Document No",

    sum("Sales Amount") 

Resident TempGeneralLedgerEntries 

Group By "Document No";

DROP Table TempGeneralLedgerEntries;

in Group By you are using Only "Document No" and in Load Script you are using other fields also .


either remove those fields from load script or else add only those fields which required in Group by,

ranibosch
Creator
Creator
Author

Can I pull the G_L Entry table again at a later stage if I want those fields?

shanky1907
Creator II
Creator II

yupp, you can pull from the database and make a different table with the same syntax as above. Please note that do not mention the field name as same as it will create sunthetic key or loops.