Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
Dear Rani,
Try Group by function:
Value entries:
Load [Document number]
Sum([Cost amount]) as Cost_Amount
Resident MainTable
Group By [Document number];
Hi Mohammed,
thanks for your response.
I'm new to Qlickview and not familiar to RESIDENT MAIN TABLE?
Hi,
Can you attach sample data file i will make it,
Thanks Mohammed.
Please see attached.
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.
Hi Shashank,
I would like to sum it in my script before I load it into the model.
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
thank you so much! it is working 100%!!
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?