Search code examples
iccube

icCube join table with ETL

I have a Customers table which contains the salesRepEmployeeNumber which is in the Employees table.

How do I do something like 

SELECT * 
FROM   Customers 
JOIN   Employees 
  ON   Customers.salesRepEmployeeNumber = Employees.employeeNumber

with icCube ETL ?

Solution

  • As pointed in another answer, you can add a table based in an SQL statement that would do the job. In case your original datasource is not able to do a join :

    We've not yet an join transformation, added this in our todo list. On the meantime, what you can do is.

    Create an Union Table with your two tables. This will create a new table with the columns of both tables. Put the small one, first as we're going to cache it later on.

    Create a Javascript view, you might need to activate Javascript in your icCube.xml configuration. In this one you can cache the first table and use a bit of js to do the join. You can trigger the table change on a field being empty. Don't forget to put 'Table Row Ordering' to Keep Table Order.

    hope it helps