Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor III

QS Data Load Table Join with Multiple Criteria

Hi, I have the below code from SQL. How do I translate this into the QS Data Load Editor? 

select a.Product_Name,
a.Manufacturer_Name,
a.SerialNumber,
a.Location_Name,
a.InventoryDate,
b.DateCollected,
b.FormName,
b.CollectedBy,
from msgat.dbo.assetsummary a join msgat.dbo.publisheddatasummary b
on a.serialnumber=b.serialnumber
where b.DateCollected = (select max(DateCollected)
from msgat.dbo.publisheddatasummary b WHERE a.SerialNumber = b.SerialNumber)
and(DateDiff(Day, b.DateCollected, a.InventoryDate) <>0
or DateDiff(Month, b.DateCollected, a.InventoryDate) <>0
or DateDiff(Year, b.DateCollected, a.InventoryDate) <>0)
and a.status<>'Disposed'
and a.serialnumber is not null
and ((a.LocationLineage LIKE '161%')
OR (a.LocationLineage LIKE '372%')
OR (a.LocationLineage LIKE '371%')
OR (a.LocationLineage LIKE '082%')
OR (a.LocationLineage LIKE 'SE2%')
OR (a.LocationLineage LIKE '154-1100%'))
ORDER BY SerialNumber

1 Solution

Accepted Solutions
New Contributor III

Re: QS Data Load Table Join with Multiple Criteria

Correction/Answer: Had to add 'SQL' prior to select statement.

View solution in original post

2 Replies
Highlighted
Valued Contributor II

Re: QS Data Load Table Join with Multiple Criteria

Have you tried following

 

LOAD
Product_Name,
Manufacturer_Name,
SerialNumber,
Location_Name,
InventoryDate,
DateCollected,
FormName,
CollectedBy,
SQL
select a.Product_Name,
a.Manufacturer_Name,
a.SerialNumber,
a.Location_Name,
a.InventoryDate,
b.DateCollected,
b.FormName,
b.CollectedBy,
from msgat.dbo.assetsummary a join msgat.dbo.publisheddatasummary b
on a.serialnumber=b.serialnumber
where b.DateCollected = (select max(DateCollected)
from msgat.dbo.publisheddatasummary b WHERE a.SerialNumber = b.SerialNumber)
and(DateDiff(Day, b.DateCollected, a.InventoryDate) <>0
or DateDiff(Month, b.DateCollected, a.InventoryDate) <>0
or DateDiff(Year, b.DateCollected, a.InventoryDate) <>0)
and a.status<>'Disposed'
and a.serialnumber is not null
and ((a.LocationLineage LIKE '161%')
OR (a.LocationLineage LIKE '372%')
OR (a.LocationLineage LIKE '371%')
OR (a.LocationLineage LIKE '082%')
OR (a.LocationLineage LIKE 'SE2%')
OR (a.LocationLineage LIKE '154-1100%'))
ORDER BY SerialNumber;

New Contributor III

Re: QS Data Load Table Join with Multiple Criteria

Correction/Answer: Had to add 'SQL' prior to select statement.

View solution in original post