Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
esuper21
Contributor III
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
esuper21
Contributor III
Contributor III
Author

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

View solution in original post

2 Replies
vvira1316
Specialist II
Specialist II

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;

esuper21
Contributor III
Contributor III
Author

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