topic Adding complex calculated field in load editor in App Development

Adding complex calculated field in load editor

jaina — Sat, 16 Nov 2024 01:39:08 GMT

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);

Re: Adding complex calculated field in load editor

RsQK — Tue, 18 Aug 2020 08:07:50 GMT

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.

Re: Adding complex calculated field in load editor

jaina — Wed, 19 Aug 2020 04:47:58 GMT

@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

Re: Adding complex calculated field in load editor

jaina — Wed, 19 Aug 2020 04:53:24 GMT

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:

Order1 1 100 DeliveryA 50 100
Order1 1 100 DeliveryB 50 0

Re: Adding complex calculated field in load editor

RsQK — Wed, 19 Aug 2020 08:20:23 GMT

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:

Re: Adding complex calculated field in load editor

jaina — Wed, 19 Aug 2020 09:30:40 GMT

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	-	-	-

Re: Adding complex calculated field in load editor

Kushal_Chawda — Wed, 19 Aug 2020 09:52:17 GMT

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.";