Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
How can I add calculated column with this formula in load editor?
Sum delivery qty based on sales order and sales order item no.
Actual Delivered Qty = sum(aggr(sum(DISTINCT [Delivery Qty]), [Sales Order],[Sales Order Item No.]))
Here is my script:
LOAD
"Sales Order",
"Sales Order Item No.",
"Order Qty"
FROM [lib://1_GMS QVDs/OTD_TARGETDELIVERY.qvd]
(qvd);
LEFT JOIN
LOAD
Delivery,
"Delivery Item No.",
"Delivery Qty",
"Sales Order",
"Sales Order Item No."
FROM [lib://1_GMS QVDs/OTD_ACTUALDELIVERY.qvd]
(qvd);
try below
Order:
LOAD
"Sales Order",
"Sales Order Item No.",
"Order Qty"
FROM [lib://Web]
(html, utf8, embedded labels, table is @1);
Join(Order)
LOAD
Delivery,
"Delivery Qty",
"Sales Order",
"Sales Order Item No."
FROM [lib://Web]
(html, utf8, embedded labels, table is @2);
Left Join(Order)
Load
"Sales Order",
"Sales Order Item No.",
sum("Delivery Qty") as Actual_Delivery
Resident Order
Group by "Sales Order",
"Sales Order Item No.";
This?
//Expected values:
Orders:
LOAD *,
"Sales Order" & '|' & "Sales Order Item No." as SalesOrderItem.#key;
LOAD
'Sales Order '& A as "Sales Order",
'Item ' & B as "Sales Order Item No.",
C as "Order Qty";
LOAD * INLINE [
A,B,C
1,1,10
1,2,50
1,3,6
2,1,33
2,2,90
3,3,100
];
//Actual values:
LEFT JOIN (Orders)
LOAD
"Sales Order" & '|' & "Sales Order Item No." as SalesOrderItem.#key,
SUM("Delivery Qty") AS "Delivery Qty"
GROUP BY "Sales Order","Sales Order Item No.";
LOAD
'Sales Order '& A AS "Sales Order",
'Item ' & B AS "Sales Order Item No.",
C AS "Delivery Qty";
LOAD * INLINE [
A,B,C
1,1,5
1,1,6
1,1,1
1,2,40
1,2,9
1,3,6
2,1,30
2,1,6
3,3,50
3,3,10
3,3,43
];
//Notice that order 2 item 2 is missing.
@RsQK Thanks for this it worked. But i just want to know how can i have an output with the same number of line items based on Delivery column?
for example, i only have 1 "Sales Order" and "Sales Order Item No." but with multiple Delivery
Sample below. It seems that I just added new field with formula in the load editor.
Table_Order:
"Sales Order" | "Sales Order Item No." | Order Qty
Order1 1 100
Table_Delivery:
"Sales Order" | "Sales Order Item No." | Delivery | Delivery Qty
Order1 1 DeliveryA 50
Order1 1 DeliveryB 50
Expected Output:
"Sales Order" | "Sales Order Item No." | Order Qty | Delivery | Delivery Qty | Actual Delivery
Order1 1 100 DeliveryA 50 100
Order1 1 100 DeliveryB 50 0
If I understood correctly, you want to keep the details. Then you shouldn't do this via data load editor - you should link the both tables instead. Heres the script:
//Expected values:
Orders:
LOAD *,
"Sales Order" & '|' & "Sales Order Item No." as SalesOrderItem.#key;
LOAD
'Sales Order '& A as "Sales Order",
'Item ' & B as "Sales Order Item No.",
C as "Order Qty";
LOAD * INLINE [
A,B,C
1,1,10
1,2,50
1,3,6
2,1,33
2,2,90
3,3,100
];
//Actual values:
Deliveries:
LOAD
Rowno() as DeliveryRow,
"Sales Order" & '|' & "Sales Order Item No." as SalesOrderItem.#key,
"Delivery Qty";
LOAD
'Sales Order '& A AS "Sales Order",
'Item ' & B AS "Sales Order Item No.",
C AS "Delivery Qty";
LOAD * INLINE [
A,B,C
1,1,5
1,1,6
1,1,1
1,2,40
1,2,9
1,3,6
2,1,30
2,1,6
3,3,50
3,3,10
3,3,43
];
//Notice that order 2 item 2 is missing.
Heres the visualization:
Hi @RsQK thanks for this, but it looks like this is not what I am trying to achieve.
Here is the visualization:
OrderQVD:
Sales Order | Sales Order Item No. | Order Qty |
Order1 | 1 | 5 |
Order1 | 2 | 10 |
Order2 | 1 | 15 |
Order2 | 2 | 20 |
Order3 | 1 | 25 |
DeliveryQVD
Delivery | Delivery Qty | Sales Order | Sales Order Item No. |
Delivery1 | 3 | Order1 | 1 |
Delivery2 | 2 | Order1 | 1 |
Delivery1 | 10 | Order2 | 1 |
Delivery2 | 5 | Order2 | 1 |
Delivery2 | 20 | Order2 | 2 |
OUTPUT
where Actual Delivery is Additional field which is equal to
=SUM(Delivery Qty) based on Sales Order and Sales Order Item No.
Sales Order | Sales Order Item No. | Order Qty | Delivery | Delivery Qty | Actual Delivery |
Order1 | 1 | 5 | Delivery1 | 3 | 5 |
Order1 | 1 | 5 | Delivery2 | 2 | - |
Order1 | 2 | 10 | - | - | - |
Order2 | 1 | 15 | Delivery1 | 10 | 15 |
Order2 | 1 | 15 | Delivery2 | 5 | |
Order2 | 2 | 20 | Delivery2 | 20 | 20 |
Order3 | 1 | 25 | - | - | - |
try below
Order:
LOAD
"Sales Order",
"Sales Order Item No.",
"Order Qty"
FROM [lib://Web]
(html, utf8, embedded labels, table is @1);
Join(Order)
LOAD
Delivery,
"Delivery Qty",
"Sales Order",
"Sales Order Item No."
FROM [lib://Web]
(html, utf8, embedded labels, table is @2);
Left Join(Order)
Load
"Sales Order",
"Sales Order Item No.",
sum("Delivery Qty") as Actual_Delivery
Resident Order
Group by "Sales Order",
"Sales Order Item No.";