Yellowfin Support Team
Category
View Builder
Product Version
8.0
join on date and timestamp field
Answered
I would like to join 2 tables in a view but the datetypes are data and timestamp (postgreSQL database). Normally I would join these using <timestamp field>::date format so I have 2 fields of the same datatype.
Is it somehow possible to replicate that in YellowFin? I know that I can do a virtual table where I join the 2 but there are more tables involved. Some more flexibility in that area might be nice to have to work around these kind of issue's.
I understand that it is partially caused by inconsequent data but that is not uncommon out there :-).
Regards,
JeRoen
The same question
Hi JeRoen,
Thanks for reaching out to support with your question. You should be able to achieve what you are looking for with a Transformation Flow, more info can be found here -
https://wiki.yellowfinbi.com/display/user80/Transformation+Steps
From the sound of it, you could utilize the merge and data conversion steps listed above to get the desired output. Does this sound like a viable solution in this case?
Thanks,
Eric
Hi JeRoen,
Thanks for reaching out to support with your question. You should be able to achieve what you are looking for with a Transformation Flow, more info can be found here -
https://wiki.yellowfinbi.com/display/user80/Transformation+Steps
From the sound of it, you could utilize the merge and data conversion steps listed above to get the desired output. Does this sound like a viable solution in this case?
Thanks,
Eric
Hello Eric,
That is not really a solution in my opinion because in that situation I would have to write an ETL step and store the data again (doublure) in another database (my main database is a readonly user) or schema.
The solution is very small and simple (a cast function in the join) but we are not able to apply it. With perfect data this issue probably does not exist but not all data is perfect :-).
Regards,
JeRoen
Hello Eric,
That is not really a solution in my opinion because in that situation I would have to write an ETL step and store the data again (doublure) in another database (my main database is a readonly user) or schema.
The solution is very small and simple (a cast function in the join) but we are not able to apply it. With perfect data this issue probably does not exist but not all data is perfect :-).
Regards,
JeRoen
Hi JeRoen,
Thanks for the reply, I think I'm getting where you're coming from here. It sounds like we have ways to do this, but there could be a better way, by supporting the CAST function natively when joining tables in Postgres SQL.
Could you provide an example syntax of a working statement, that doesn't work in Yellowfin?
When you say "not able to apply," do you receive any error messages? Could you provide the logs for analysis, with a timestamp of the attempted function?
I'm not seeing anything in our developer platform that relates to this so far, we may end up having to create a developer request to look into this.
Thanks,
Eric
Hi JeRoen,
Thanks for the reply, I think I'm getting where you're coming from here. It sounds like we have ways to do this, but there could be a better way, by supporting the CAST function natively when joining tables in Postgres SQL.
Could you provide an example syntax of a working statement, that doesn't work in Yellowfin?
When you say "not able to apply," do you receive any error messages? Could you provide the logs for analysis, with a timestamp of the attempted function?
I'm not seeing anything in our developer platform that relates to this so far, we may end up having to create a developer request to look into this.
Thanks,
Eric
Hello Eric,
My initial example is a good example. We have a dim_date table with only date fields (and text, integers also but those are not relevant). I have a measure table where I have a timestamp field for a datetime (using PostgreSQL database).
I want to join them so I know everything date related there is to know (weekday, weekendday, week number, dayname, ect) to the "rap_dat" field in my measure table. This "rap_dat" field is in prostgreSQL of the type timestamp. The dim_date table does not have timestamp fields but only date fields.
When I try to join the 2 YellowFin recognizes that one table does not has a timestamp field so in the fieldlist of the measure table that field is not even shown!! Only fields with a datatype that is present in both tables in the join are presented. So in affect I can not join the tables. I could use a virtual table but with more tables to be joined and all the limitation of what you can do with a virtual table that is not a solution I want to persue.
I hope I made it clear.
Regards,
JeRoen
Hello Eric,
My initial example is a good example. We have a dim_date table with only date fields (and text, integers also but those are not relevant). I have a measure table where I have a timestamp field for a datetime (using PostgreSQL database).
I want to join them so I know everything date related there is to know (weekday, weekendday, week number, dayname, ect) to the "rap_dat" field in my measure table. This "rap_dat" field is in prostgreSQL of the type timestamp. The dim_date table does not have timestamp fields but only date fields.
When I try to join the 2 YellowFin recognizes that one table does not has a timestamp field so in the fieldlist of the measure table that field is not even shown!! Only fields with a datatype that is present in both tables in the join are presented. So in affect I can not join the tables. I could use a virtual table but with more tables to be joined and all the limitation of what you can do with a virtual table that is not a solution I want to persue.
I hope I made it clear.
Regards,
JeRoen
Hello Simon,
Thanks for following up. I have a workaround using virtual tables so my immediate problem is fixed. I think more advanced join options is beneficial for both YF and the enduser but I let YF decide if this is needed as I have a working workaround.
Regards,
JeRoen
Hello Simon,
Thanks for following up. I have a workaround using virtual tables so my immediate problem is fixed. I think more advanced join options is beneficial for both YF and the enduser but I let YF decide if this is needed as I have a working workaround.
Regards,
JeRoen
Replies have been locked on this page!