We currently have an inventory analysis application that compares inventory on hand to- sales, sales returns, sales credits, Point-of Sales, and backlog(open orders).
With the exception of backlog(open orders), data is linked using a key Customer No + Part No + Date
Data is agrregated by Customer no, part no, month, year.
The problem is that we don't know how link all open orders for customer no, part no. We really don't want to consider a specific time. We would like to sum up all open orders regardless of date, but still allow a period selection for calculated expression for inventory, sales, etc....
We do have a req. date for open orders that we could add to customer no and part no and use in the same key as all the other data, but that causes a problem for selections.
We have also tried to create a second key without date, but this affects the front end selection and does not associate to the other data.
Script with standard key
Backlog_Temp:
LOAD
applymap
('Cust_Map', SALES_CUST, 'MISSING')
AS BParent_Number
,
trim
(text(GPN))
AS BGPN
,
REQ_QTY
,
LOCAL_SALES_PRICE
* REQ_QTY
AS BV
,
monthstart
(date(CUSTOMER_REQ_DATE))
AS BDate
FROM
.. \QVD\BACKLOG_DETAIL.QVD (qvd
)
WHERE
EXISTS(CUSTDA,SALES_CUST)
;
Backlog_Sum:
//Summarize Backlog
BParent_Number
& '|' & BGPN & '|' & BDate
AS BKey
,
BParent_Number
,
BGPN
AS BPart
,
BDate
,
SUM
(REQ_QTY)
AS Backlog_Qty
,
SUM
(BV)
AS Backlog_Amount
RESIDENT
Backlog_Temp
GROUP
BY BParent_Number, BGPN,
BDate;
DROP
TABLE Backlog_Temp;
Script with no date
LOAD
Backlog_Temp:
LOAD
applymap
('Cust_Map', SALES_CUST, 'MISSING') AS BParent_Number,
trim
(text(GPN)) AS BGPN,
REQ_QTY
,
LOCAL_SALES_PRICE
* REQ_QTY AS BV
FROM
.. \QVD\BACKLOG_DETAIL.QVD (qvd)
WHERE
EXISTS(CUSTDA, SALES_CUST);
Backlog_Sum:
//Summarize Backlog
LOAD
BParent_Number
& '|' & BGPN & '|' AS BKey,
BParent_Number
,
BGPN
,
SUM
(REQ_QTY) AS Backlog_Qty,
SUM
(BV) AS Backlog_Amount
RESIDENT
Backlog_Temp
GROUP
BY BParent_Number, BGPN;
DROP
TABLE Backlog_Temp;
Should we handle this in the load script or on the front end?
Help is greatly appreciated
