topic Re: Create new table using existing Data, then Group by Number in QlikView

Create new table using existing Data, then Group by Number

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Create new table using existing Data, then Group by Number

Gysbert_Wassenaar — Mon, 12 Jun 2017 17:21:02 GMT

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

;

Re: Create new table using existing Data, then Group by Number

Tue, 13 Jun 2017 07:53:40 GMT

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?

Re: Create new table using existing Data, then Group by Number

jonathandienst — Tue, 13 Jun 2017 09:20:33 GMT

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

What are you attempting to do?

Re: Create new table using existing Data, then Group by Number

Tue, 13 Jun 2017 11:54:39 GMT

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>