I am trying to append some returns information into an existing service level report.
I did not build the original data model, and I'm trying to append into the existing - but I can't seem to get it to work, or work out why.
I have the sales/shorts data in a table called ShipLines
ShipLines:
LOAD [Auth_ Adj_ Qty_],
[Auth_ Adj_ Qty_] * [Qty_ per Unit of Measure] as Actual_Shorts,
[Auth_ Adj_ Reason Code],
[Location Code],
text(No_) as No_,
text(No_) as LINK_Item,
[No_ of Pallets],
[Order Line No_],
[Order No_],
[Order Type],
[Posting Date],
date([Posting Date],'YYYYMMDD') as LINK_Calendar,
[Promised Delivery Date],
[Purch_ Order Line No_],
[Purchase Order No_],
[Qty_ Invoiced (Base)],
[Qty_ per Unit of Measure],
[Qty_ Shipped Not Invoiced],
Quantity,
[Quantity (Base)],
[Quantity Invoiced],
[Quantity Invoiced]* [Qty_ per Unit of Measure] as Actual_Invoiced,
date([Requested Delivery Date],'DD/MM/YYYY') as [Requested Delivery Date],
text([Sell-to Customer No_]) as [Sell-to Customer No_],
text([Sell-to Customer No_]) as LINK_Customer
FROM
[D:\datasources\qvd\ShipLine.qvd]
(qvd)
where Type='2' and not match(mid([Order No_],2,3),'POR');
I have the returns information recorded against the original order
LOAD
// Site,
// "Return No",
"Date logged",
date([Date logged],'YYYYMMDD') as LINK_Calendar,
// Haulier,
// Customer,
"Order No" as OrderNo,
"Order No" as No_,
text("Order No") as JOIN_OrderNo,
// Site & '-' & text("Product Code") & '-' & Customer & '-' & "Order No" & '-' & "Reason code / Details" as "LINK_Returns",
text(repeat('0', 4-len("Product Code")) & "Product Code") as "LINK_Item",
// if(wildmatch(Comments,'*OFF SITE DISPOSAL*'), 'DISPOSED','RTN' & "Order No") as "ReturnReference",
// "Reason code / Details",
Cases as Returned_Cases
// Plt,
// // "Product Code",
// "BBE Date",
// "Returning Y/N",
// "Authorise of Ret",
// "Job num",
// "Estimated Return Date",
// "Date Rec'd",
// "Responsibility",
// Comments,
// "Date processed on WMS",
// Status,
// "TSR Number",
// Claim,
// "Damage Log"
FROM [D:\datasources\ReturnsLog.qvd]
(qvd);
and I pull the customer (Sell to) from the Order Header table.
OrderHeader:
LOAD distinct
text("OrderNo") as "JOIN_OrderNo",
text("Sell-to Customer No_")
FROM [D:\datasources\PostedSalesOrderHeader.qvd]
(qvd) where exists (OrderNo);
I cannot for the life of me get the data by Customer (Sell to) to be assosciated to LINK_Customer. I've tried joins, concatenates and maps - but it just doesn't seem to work.
