You can translate the question and the replies:

Field expressions guide

I'm trying to find a good guide on writing field expressions for new fields in derived views. I've looked through the admin, developer and advanced VQL guide and still can't find what I'm looking for. What I basically want to do is create a new field in my derived view and have an expression that's something like this ***employee.employee_name where employee.employee_id = sales.employee_id*** Any help would be appriciated
derived view Regular Expressions Expression
user
29-03-2017 12:44:48 -0400
code

4 Answers

You would first need to create a join view that joined the employee and sales table on the employee_id. Then in your output, you choose the employee_name field coming from the employee table. By default it will grab all fields from both tables in a join - just remove the fields you don't need.
user
29-03-2017 12:59:26 -0400
Thanks ver much for your answer. I currently have a join view but in the sales table I have multiple employeeid's one for the actual sales agent and anouther for customer relationship manager who tend to be two different people, I would like to output the names of both employees in the derived view. So I would like to have two fields which looks something like this. Sales agent employee.employee_name where employee.employee_id = sales.sales_employee_id Customer relationship manager employee.employee_name where employee.employee_id = sales.relationship_manager_employee_id
user
29-03-2017 13:33:21 -0400
In that case you'd want 2 joins inside your dervied view - you'll bring in employee 2 times and sales 1 time. Make sales join to the first employee view by employee_id = sales_employee_id. Join sales to the 2nd employee view by employee_id = relationship_manager_employee_id. Then in your output, formula for sales agent would be employee1.employee_name. The formula for customer manager would be employee2.employee_name. Denodo will alias the 2 employee views differently - off the top of my head by adding a number to the end. It's basically the same way you'd do it in regular SQL if you're familiar with that. You have 2 instances of the employee view b/c you have different joins, so you alias them differently. Then in your select, you just reference the correct alias which represents the correct join.
user
29-03-2017 13:56:35 -0400
That's done the trick, thanks very much
user
30-03-2017 06:53:24 -0400
You must sign in to add an answer. If you do not have an account, you can register here