Group by Purchase order to sum to single line

[#1]

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

[sunny_talwar]

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?

[#3]

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.

[sunny_talwar · Accepted solution]

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;