Hi! I have a task where the end goal is to get a table where i see, for each sale order; either if it is an online order or who is the salesperson. The store sells online and directly (salesperson). I want the final table to look like this:
(example)
OrderID ---ResponsibleForSale
41424 John Johnson
12443 Online sales
I have one document, salesorders, where I have the Order IDs. In this document I also have a flag which is 1 if it is a online sale. This document is connected to territoryhistory-document through territoryID. The territory-document is then connected to the employee-doc through employeeID. In this doc we have the Salespersons name. If the employee is a salesperson the employee allways have employee ID above 273.
I tried with this script below, but it doesn´t work yet. (So the goal is; if onlineflag=1 - then write "online sales" on that order-number row. If onlineflag=0 and employee ID > 273 (so it is a salesperson). Then write the sale persons name. Think about that they are not directly connected but connected through 1 doc in between.
RepresentationList:
LOAD
SalesOrderID,
OrderDate,
OnlineOrderFlag,
TerritoryID,
IF(OnlineOrderFlag=1,'Online Sales', 0) as OrderFrom
FROM [lib://QVD/SalesOrderHeader.qvd]
(qvd);
Inner Join
Table2:
LOAD
BusinessEntityID,
TerritoryID
FROM [lib://QVD/SalesTerritoryHistory.qvd]
(qvd)
where Exists(TerritoryID,TerritoryID);
Inner Join
Table3:
LOAD
EmployeeBusinessEntityID,
IF(EmployeeBusinessEntityID>273 AND OnlineOrderFlag=0,EmergencyContactName,OrderFrom) as ListForSalesResp
FROM [lib://QVD/Employee.qvd]
(qvd)
Where Exists(BusinessEntityID,EmployeeBusinessEntityID);
