How can we Join of two tables using Source Lookup with a filter Condition in QLIK Replicate if source is MS-SQL

[Dileep_41]

Is there a way we can Join two tables in Filter condition using  SOURCE_LOOKUP function in QLIK Replicate without enabling FULL LOAD PASSTHRU filter. If we do, what would be the appropriate syntax in filter condition we need to pass?

Example:

SOURCE - MS SQL Server

Source tables: dbo.Employee, dbo.Department

Target table: dbo.Employee

Join Condition: Employee INNER JOIN Department on Employee.ID = Department.Employee_ID where DEPARTMENT_NAME = 'HR';

Please let me know if we have any approach for the above conditions to be met where CDC is enabled on source and we need the change processing to be captured Realtime?

[john_wang]

Hello @Dileep_41 ,

Thanks for reaching out!

Good Question! Replicate source_lookup() function is powerful and scalable for multiple tables join, it can be used for any custom SQLs. Besides the simple sample in User Guide, here is a more complex sample (inner join Employees table and Orders table ) 

source_lookup('NO_CACHING','x','y','o.shipRegion FROM Northwind.dbo.Employees e, Orders o WHERE e.EmployeeID = o.EmployeeID and o.EmployeeID=? --', 'z',$EmployeeID)

where 'x','y','z' are dummy input parameters, they will be ignored by Replicate query.  In this sample column  dbo.Orders.ShipRegion will be retried and add to table dbo.Employees table, as below:

the results looks like:

If you have more complex expression, or it's hard to compose such a complicated SQL inside Replicate expression, you may create a VIEW in source DB and then call it in source_lookup() function just as it's a regular table.

Hope this helps.

Regards,

John.

[Dileep_41]

Hi John,

Thank you so much for the detailed explanation. I have tried the above solution you have suggested but I am getting the below error while running the QLIK task:

Error message: "Failed to send an end of data record (ID=2) for table with ID '1'."

Where are we passing  the EmployeeID filter to fetch only those records based on the condition?

And also could you please let me know if there is a way to join the two tables and fetch the target data without adding any new column in the target like below?

Condition: dbo.Employees e join  Orders o WHERE e.EmployeeID = o.EmployeeID and o.shipRegion = 'RJ';

[john_wang]

Hello @Dileep_41 ,

If you want to transfer a fixed value (eg o.shipRegion = 'RJ') to WHERE sub-clause then you may use below sample:

source_lookup('NO_CACHING','x','y','o.shipRegion FROM dbo.Employees AS e INNER JOIN dbo.Orders AS o ON e.EmployeeID = o.EmployeeID and o.shipRegion=? --', 'z','RJ')

If you facing errors then the best way is checking the task log file to understand what SQL was composed and then adjust your expression to manager it to work.

BTW,  I'm not very sure what's the "without adding any new column in the target" meaning, maybe you can replace a target column value by above expression however if you do not use the return column anywhere then why you use such expression? or maybe a filter is more accurate for you.

Regards,

John.

[Dileep_41]

Hello John,

Thank you for the quick response.  I was looking for the above join condition with source_lookup function to be used in the filter and is working fine as expected with a little tweak of your syntax.

Filter condition: source_lookup('NO_CACHING','dbo','Orders','EmployeeID','EmployeeID=? and shipRegion in (?)',$EmployeeID,'RJ')

Could you please let me know the syntax to be used in filter condition for an inner join or left join?

BTW..can we do join of multiple tables in the filter similarly, Please suggest!

Regards,

Dileep Kumar.