Create new table using existing Data, then Group by Number

[#1]

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	1000
5465046504564	Mar	2017	1000
5465046504564	Mar	2017	1000
5465046504564	Mar	2017	1000

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

[Gysbert_Wassenaar]

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

;

[#3]

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?

[jonathandienst]

This is bad syntax:  Max([Net price][,1])

What are you attempting to do?

[#5]

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>