Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have created the below table but the purchase order is across multiple lines the purchase order value [Net Price] remains the same but split every time invoiced. so table looks like below.
PurchaseOrderNumber | Month | Year | [Net Price] | |
---|---|---|---|---|
5465046504564 | Jan | 2017 | 1000 | |
5465046504564 | Feb | 2017 |
| |
5465046504564 | Mar | 2017 |
| |
5465046504564 | Mar | 2017 |
| |
5465046504564 | Mar | 2017 |
|
LOAD [Posting date],
month ([Posting date]) as Month,
Year ([Posting date]) as Year,
Doc.number,
[Purchasing document] as PurchaseOrderNumber,
[PO DOC TYPE],
Vendor as VendorID,
F8 as VendorName,
[Vendor Material],
[PO - Material Line Item Text1],
Item,
[Product Description],
[Material group]as MaterialGroup,
F14 as MaterialGroupName,
Material,
[Cost Center],
[Person responsible],
[Net price],
[PO quantity] as PurchaseOrderQTY,
[Invoiced Value]
FROM XX;
****** The above code is what i want to analyse however need to create below from above code, can you please help***
PurchaseOrderHeader:
Load
Month,
Year,
PurchaseOrderNumber,
Max([Net price][,1]) AS TotalPurchaseOrderValue
Resident PurchaseOrderDetails Group By PurchaseOrderNumber,;
To display New Table like below
PurchaseOrderNumber | Month | Year | TotalPurchasOrderNumber |
---|---|---|---|
5465046504564 | Jan | 2017 | 1000 |
If you want to group by only the PurchaseOrderNumber then you must use an aggregation function on all other fields. In this case that's Month and Year as well:
PurchaseOrderHeader:
Load
PurchaseOrderNumber,
Min(Month) AS Month,
Min(Year) AS Year,
Max([Net price]) AS TotalPurchaseOrderValue
Resident
PurchaseOrderDetails
Group By
PurchaseOrderNumber
;
Thank you for your prompt Response Gysbert, Really thank you.
I have used the above code and get an expression error;
Error in expression:
')' expected
PurchaseOrderHeader:
Load
PurchaseOrderNumber,
Min(Month) AS Month,
Min(Year) AS Year,
Max([Net price][,1]) AS TotalPurchaseOrderValue
Resident
PurchaseOrderDetails
Group By
PurchaseOrderNumber
Any Idea's?
This is bad syntax: Max([Net price][,1])
What are you attempting to do?
Group My PurchaseOrder Lines from the created Table in turn creating a New Table with a single line, Grouping by PurchaseOrder. with a MAX Value of the PurchaseOrder at the earliest PurchaseOrder creation.
I hope this makes sense Dienst>