You can translate the question and the replies:

Join Views with fields that have similar data, but not identical

Hello, I'm new to the Deonodo coummunity. I'm currently working on a poc using two Exel datasources I've created and built views on top of. I am trying to join two of these views together based on a field that contains a calendar quarter. In one field I have the data represented as: q1, q2, q3, q4 to represent quarters. In the other view I have data respresented as: Qtr 1, Qtr 2, Qtr 3, Qtr 4. Both fields are text datatypes. In traditional SQL, there are a few methods I could use to join these together easily. However, I am having trouble joining these views with the given operators. Is it possible to join them together through Denodo, or am I expected to transform the data outside of Denodo before importing?
user
28-12-2021 09:27:34 -0500

1 Answer

Hi, Denodo offers complex transformation capabilities before loading. For you scenario, perform the following for joining when the data are similar: * Create a selection view over the base view which contains data **Qtr1,Qtr2,Qtr3,Qtr4**. * Navigate to output tab and click the pencil icon to edit the field expression. * Use a case statement for converting the data **Qtr1** as **q1**. For example. * Case * When(baseview.“column_name”=’Qtr1’) then ‘q1’ * When(baseview.“column_name”=’Qtr2’) then ‘q2’ * When(baseview.“column_name”=’Qtr3’) then ‘q3’ * When(baseview.“column_name”=’Qtr4’) then ‘q4’ * Else ‘ ‘ end. * Use this selection view and join with the base view which contains data **q1,q2,q3,q4**. For further information, refer to **[Join Operation](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/queries_select_statement/from_clause/join_operation#join-operation)** and **[CASE Clause Examples](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/appendix/case_clause_examples/case_clause_examples#case-clause-examples)** document from the Virtual DataPort VQL Guide. Hope this helps!
Denodo Team
29-12-2021 05:25:21 -0500
You must sign in to add an answer. If you do not have an account, you can register here