Can I create a column in Yellowfin at view level to be involved in a join?

Nhi Tran shared this question 11 months ago
Answered

Hi Yellowfin Support,

Just wondering if the possible scenario can take place in Yellowfin.

Can I create a new column in the Yellowfin view ontop of a table BUT that column needs to be involved in one of the joins?

For example, I have a table X in my yellowfin view which has a date/timestamp column Y. I need to join just the date of column Y to another table B on the view level whose column does not contain a timestamp. Can I somehow create another column in the Yellowfin view on table X that truncates column Y so that it can be used in the join to table B?


Thank you

Comments (5)

photo
1

Hi Nhi,


Yes this is possible. To do this, you will use a Calculated field with a Date Hierarchy function to truncate X down to just a date value. To do this edit your view and on Step 2 where you can edit your fields, expand Calculated Fields (1 on image), open up Date Hierarchy and drag the Date (2 on image) field into your Available Fields list. From here, click on Date to select your column Y as the field to base it on (3 on image). See the image below for reference.


0fe09e5e70e0961cdd8ff7fe8735396c

After this, move to the Format tab to ensure it is a date and in the format you require (see below)

6bec648530fb7f75f6fd89f62dbf2725

You can then use this new Date field to complete your join using just the date (no timestamp). Please let me know if you have any issues with this or if there is anything else I can do to help.


Cheers,

Neal

photo
1

Thanks Neal, but we were wondering if you could do that at join level (step 1) before having to get to step 2 as we wanted the truncated column to be involved in the inner join of the tables.

photo
1

Hi Nhi,


My apologies for not understanding that correctly. The only way to do this would be to create table X as a virtual table and use a SQL function included in your particular datasource type to truncate the date. For example, the Virtual Table SQL could be:


select City, End_Time, cast(End_Time As Date) as End_Date from mydata


The above example would give you the End_Date column to join with other tables in the view.


Cheers,

Neal

photo
1

Thank you Neal. We tried to resolve this internally at the database level, so this ticket can be marked as resolved.

photo
1

Hi Nhi,


Okay, thank you for letting me know. If you have any further questions please let me know.


Cheers,

Neal