Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
So ill describe my SQL scenario! 🙂
I have a table that holds customer orders in a database called dbo.Orders
I have an excel sheet that holds customer orders that have yet to be entered into the database called SmartSheet.xlsx. Since this file is historical data over time, its possible to have an order in both the SmartSheet.xlsx and dbo.Orders table.
I would like to merge these two together, then be able to show how many orders from SmartSheet are not in the dbo.Orders table.
In SQL I would write something like :
Select * from dbo.Orders a
Full outer join SmartSheet.xlsx b on a.OrderID = b.OrderID
where a.OrderID is null
In Qlik, I have loaded my data like this:
Table A:
Load *;
SQL
Select
OrderID,
Data
from dbo.Orders;
Table B:
Load
OrderID,
Date
from SmartSheet.xlsx;
When i perform the above, I get the two tables merged on the same conditions (this case OrderID and Date) but I don't know how to FILTER on those Orders from Table B that are not found in Table A.
What should i do?
If you write NoConcatenate before your LOAD statement, you will have two intact tables. Rename Date in SQL to Date_SQL so Qlik Sense can automatically link the two tables based on Order_ID.
In the App create a table and choose Order_ID and Date_SQL as dimensions. The Order_ID should return a NULL where no Date_SQL was found, thus showing you which Order is not part of your database.