icCube join table with ETL

1
votes

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 ?

2
iccube

2 Answers

2
votes

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

0
votes

No need to use the ETL.

With the designer, add a table with the + sign in the menu above DataSource. The next panel gives you the choice between reading data from an existing table or an sql query.