Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
margretand
Contributor
Contributor

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. 

 

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

 

 

 

 

Labels (2)
2 Replies
margretand
Contributor
Contributor

Two problems I have identified so far with output from the script:

1. I get several rows for same order-ID. So it doesn´t seem to connect and instead giving the possible salesperson for each order-ID.  (like below)

2. In this row it is not recognizing "Orderfrom"; IF(EmployeeBusinessEntityID>273 AND OnlineOrderFlag=0,EmergencyContactName,OrderFrom) as ListForSalesResp

Tried to define it before but this is not working. Any suggestions or suggestion on different approach for this problem?

 

4422   Michael

4422    Jordan

4422     Peter

margretand
Contributor
Contributor

No suggestions?