topic Re: QS Data Load Table Join with Multiple Criteria in Connectivity & Data Prep

QS Data Load Table Join with Multiple Criteria

esuper21 — Thu, 17 Jan 2019 13:12:31 GMT

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

Re: QS Data Load Table Join with Multiple Criteria

vvira1316 — Thu, 17 Jan 2019 13:41:39 GMT

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;

Re: QS Data Load Table Join with Multiple Criteria

esuper21 — Wed, 23 Jan 2019 20:09:40 GMT

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