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: 
Not applicable

Create new table using existing Data, then Group by Number

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

MonthYear[Net Price]
5465046504564Jan20171000
5465046504564Feb2017
1000
5465046504564Mar2017
1000
5465046504564Mar2017
1000
5465046504564Mar2017
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

PurchaseOrderNumberMonthYearTotalPurchasOrderNumber
5465046504564Jan20171000
4 Replies
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

;


talk is cheap, supply exceeds demand
Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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

What are you attempting to do?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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>