topic Sum a select statement in App Development

Sum a select statement

ranibosch — Tue, 17 Oct 2017 08:25:40 GMT

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?

Re: Sum a select statement

mdmukramali — Tue, 17 Oct 2017 09:48:15 GMT

Dear Rani,

Try Group by function:

Value entries:

Load [Document number]

Sum([Cost amount]) as Cost_Amount

Resident MainTable

Group By [Document number];

Re: Sum a select statement

ranibosch — Tue, 17 Oct 2017 10:00:20 GMT

Hi Mohammed,

thanks for your response.

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

Re: Sum a select statement

mdmukramali — Tue, 17 Oct 2017 10:09:30 GMT

Hi,

Can you attach sample data file i will make it,

Re: Sum a select statement

ranibosch — Tue, 17 Oct 2017 10:19:55 GMT

Thanks Mohammed.

Please see attached.

Re: Sum a select statement

shanky1907 — Tue, 17 Oct 2017 10:24:23 GMT

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.

Re: Sum a select statement

ranibosch — Tue, 17 Oct 2017 10:40:43 GMT

Hi Shashank,

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

Re: Sum a select statement

shanky1907 — Tue, 17 Oct 2017 10:49:09 GMT

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

Re: Sum a select statement

ranibosch — Tue, 17 Oct 2017 10:59:42 GMT

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

Re: Sum a select statement

ranibosch — Tue, 17 Oct 2017 12:04:33 GMT

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?

Re: Sum a select statement

shanky1907 — Tue, 17 Oct 2017 12:17:28 GMT

Yes, you can do that. Same way..

Re: Sum a select statement

ranibosch — Tue, 17 Oct 2017 12:35:08 GMT

can one have a where statement in a resident load?

Re: Sum a select statement

mdmukramali — Tue, 17 Oct 2017 12:41:58 GMT

Hi,

Yes you can write where condition in Resident Load.

Re: Sum a select statement

ranibosch — Tue, 17 Oct 2017 12:58:09 GMT

what is wrong with this script?

it seems not to be working...

Re: Sum a select statement

mdmukramali — Tue, 17 Oct 2017 13:04:51 GMT

//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,

Re: Sum a select statement

ranibosch — Tue, 17 Oct 2017 13:09:50 GMT

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

Re: Sum a select statement

shanky1907 — Tue, 17 Oct 2017 13:25:33 GMT

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.