Can I create a column in Yellowfin at view level to be involved in a join?
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
The same question
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.
After this, move to the Format tab to ensure it is a date and in the format you require (see below)
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
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.
After this, move to the Format tab to ensure it is a date and in the format you require (see below)
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
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.
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.
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
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
Thank you Neal. We tried to resolve this internally at the database level, so this ticket can be marked as resolved.
Thank you Neal. We tried to resolve this internally at the database level, so this ticket can be marked as resolved.
Hi Nhi,
Okay, thank you for letting me know. If you have any further questions please let me know.
Cheers,
Neal
Hi Nhi,
Okay, thank you for letting me know. If you have any further questions please let me know.
Cheers,
Neal
Replies have been locked on this page!