Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlik'ers,
Trying to group Purchase orders from the below script and table the sum will add the invoice values to a single line.
PurchasingOrderNumber | PurchaseOrderValue | Invoiced Value |
4700000890 | £ 1,220,000.00 | £ 100.00 |
4700000918 | £ 27,000.00 | £ 111.15 |
4700001071 | £ 20,000.00 | £ 990.00 |
4700001213 | £ 60,000.00 | £ 204.00 |
4700001213 | £ 60,000.00 | £ 138.00 |
4700001213 | £ 60,000.00 | £ 111.00 |
4700001213 | £ 60,000.00 | £ 66.00 |
4700001385 | £ 108,000.00 | £ 1,273.98 |
4900000302 | £ 5.00 | £ 5.00 |
4900000348 | £ 5.00 | £ 5.00 |
4900000535 | £ 10.00 | £ 10.00 |
4900000847 | £ 928,422.00 | £ 928,422.00 |
6500162203 | £ 5,000.00 | £ 11.93 |
6500184737 | £ 50,000.00 | £ 117.45 |
6500184737 | £ 50,000.00 | £ 116.29 |
6500184737 | £ 50,000.00 | £ 30.63 |
6500184737 | £ 50,000.00 | £ 13.64 |
6500184737 | £ 50,000.00 | £ 12.63 |
6500184737 | £ 50,000.00 | £ 12.01 |
6500184737 | £ 50,000.00 | £ 10.21 |
6500184739 | £ 40,000.00 | £ 81.26 |
6500187977 | £ 5,000.00 | £ 16.68 |
To..........
PurchasingOrderNumber | PurchaseOrderValue | Invoiced Value Total |
4700000890 | £ 1,220,000.00 | £ 100.00 |
4700000918 | £ 27,000.00 | £ 111.15 |
4700001071 | £ 20,000.00 | £ 990.00 |
4700001213 | £ 60,000.00 | £ 519.00 |
4700001385 | £ 108,000.00 | £ 1,273.98 |
4900000302 | £ 5.00 | £ 5.00 |
4900000348 | £ 5.00 | £ 5.00 |
4900000535 | £ 10.00 | £ 10.00 |
4900000847 | £ 928,422.00 | £ 928,422.00 |
6500162203 | £ 5,000.00 | £ 11.93 |
6500184737 | £ 50,000.00 | £ 312.86 |
6500184739 | £ 40,000.00 | £ 81.26 |
6500187977 | £ 5,000.00 | £ 16.68 |
PurchaseOrderdetails:
LOAD [Posting date],
Month([Posting date]) as PurchaseOrderMonth,
Year([Posting date]) as PurchaseOrderYear,
Doc.number,
[Purchasing document] as PurchaseOrderNumber,
[PO DOC TYPE],
Pick(Match([PO DOC TYPE],'FO','ZINV','ZC2P','ZWGA','ZSTK','NB','ECPO'),
'FrameworkOrder','InvoicingPlan','C2P','WGA','Stock/Inventory','OTV','CatalogueOrder') as PurchaseOrderType,
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] as PurchaseOrderValue,
[PO quantity] as PurchaseOrderQTY,
[Invoiced Value]
FROM
May be this
PurchaseOrderdetails:
LOAD [Posting date],
Month([Posting date]) as PurchaseOrderMonth,
Year([Posting date]) as PurchaseOrderYear,
Doc.number,
[Purchasing document] as PurchaseOrderNumber,
[PO DOC TYPE],
Pick(Match([PO DOC TYPE],'FO','ZINV','ZC2P','ZWGA','ZSTK','NB','ECPO'),
'FrameworkOrder','InvoicingPlan','C2P','WGA','Stock/Inventory','OTV','CatalogueOrder') as PurchaseOrderType,
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] as PurchaseOrderValue,
[PO quantity] as PurchaseOrderQTY,
[Invoiced Value]
FROM
AggregatedTable:
LOAD PurchaseOrderNumber,
PurchaseOrderValue,
Sum([Invoiced Value]) as [Invoice Value Total]
Resident PurchaseOrderdetails
Group By PurchaseOrderNumber, PurchaseOrderValue;
What about the other fields you have in your load, you don't need them anymore? or are you imagining this to be another table which will include the aggregated data by PurchaseOrderNumber?
Hi Sunny,
This will be a separate Table to identify the % of invoiced Value vs PO Value, but delivered from the same data source as the script enclosed.
May be this
PurchaseOrderdetails:
LOAD [Posting date],
Month([Posting date]) as PurchaseOrderMonth,
Year([Posting date]) as PurchaseOrderYear,
Doc.number,
[Purchasing document] as PurchaseOrderNumber,
[PO DOC TYPE],
Pick(Match([PO DOC TYPE],'FO','ZINV','ZC2P','ZWGA','ZSTK','NB','ECPO'),
'FrameworkOrder','InvoicingPlan','C2P','WGA','Stock/Inventory','OTV','CatalogueOrder') as PurchaseOrderType,
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] as PurchaseOrderValue,
[PO quantity] as PurchaseOrderQTY,
[Invoiced Value]
FROM
AggregatedTable:
LOAD PurchaseOrderNumber,
PurchaseOrderValue,
Sum([Invoiced Value]) as [Invoice Value Total]
Resident PurchaseOrderdetails
Group By PurchaseOrderNumber, PurchaseOrderValue;