Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jaina
Contributor III
Contributor III

Adding complex calculated field in load editor

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

 

Labels (1)
1 Solution

Accepted Solutions
Kushal_Chawda

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

 

Annotation 2020-08-19 105148.png

View solution in original post

6 Replies
RsQK
Creator II
Creator II

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.

jaina
Contributor III
Contributor III
Author

@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

jaina
Contributor III
Contributor III
Author

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

RsQK
Creator II
Creator II

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:

aaaa.png

jaina
Contributor III
Contributor III
Author

Hi @RsQK  thanks for this, but it looks like this is not what I am trying to achieve.

 

Here is the visualization:
OrderQVD:

Sales OrderSales Order Item No.Order Qty
Order115
Order1210
Order2115
Order2220
Order3125

 

DeliveryQVD

DeliveryDelivery QtySales OrderSales Order Item No.
Delivery13Order11
Delivery22Order11
Delivery110Order21
Delivery25Order21
Delivery220Order22

 

OUTPUT

where Actual Delivery is Additional field which is equal to

=SUM(Delivery Qty) based on Sales Order and Sales Order Item No.

Sales OrderSales Order Item No.Order QtyDeliveryDelivery QtyActual Delivery
Order115Delivery135
Order115Delivery22-
Order1210---
Order2115Delivery11015
Order2115Delivery25 
Order2220Delivery22020
Order3125---
Kushal_Chawda

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

 

Annotation 2020-08-19 105148.png