Join columns from different tables based on criterias.
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.
LOAD SalesOrderID, OrderDate, OnlineOrderFlag, TerritoryID, IF(OnlineOrderFlag=1,'Online Sales', 0) as OrderFrom
FROM [lib://QVD/SalesOrderHeader.qvd] (qvd);
Table2: LOAD BusinessEntityID, TerritoryID FROM [lib://QVD/SalesTerritoryHistory.qvd] (qvd)
LOAD EmployeeBusinessEntityID, IF(EmployeeBusinessEntityID>273 AND OnlineOrderFlag=0,EmergencyContactName,OrderFrom) as ListForSalesResp FROM [lib://QVD/Employee.qvd] (qvd) Where Exists(BusinessEntityID,EmployeeBusinessEntityID);