join on date and timestamp field

JeRoen shared this question 6 months ago
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

Comments (8)

photo
1

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

photo
1

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

photo
1

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

photo
1

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

photo
1

Hi JeRoen,


Thanks for your patience on this, I've picked this one up from Eric.


I think that you and Eric have gone over the existing solutions that Yellowfin is capable of at this stage, in terms of joining tables in the View on two different Data Types. I think Virtual Tables or setting up an ETL flow would be the most optimal solutions, obviously dependant on your environment.


After some research (I'm not too familiar with Postgres), I understand the functionality you are looking for, and I would be happy to raise this as a candidate for an Enhancement Request. I don't believe what you are asking for is possible in Yellowfin at this stage.


I'll give this some more thought and collaborate with my colleagues to see if they can think of a work around.


Kind regards,

Simon

photo
1

Hi JeRoen,


I'm just checking in to see how you are travelling. As I've mentioned in the previous message, the feature you are asking for would be considered an Enhancement Request, where I can create an Idea post on your behalf if you would like to go down this route. Let me know if this is what you would like, otherwise, let me know if you have any other questions.


Kind regards,

Simon

photo
1

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

photo
1

Hi JeRoen,


I'm glad you managed to find a workaround!


I've gone ahead and made an Idea post on your behalf, which can be found here. This is where I will post any updates regarding the Enhancement Request, and where other members of the Community can register their interest!


In the meantime, seeing as you have a functional workaround, would you like me to go ahead and mark this ticket as completed? If you have any lingering questions, please feel free to send them through and I will answer as best as I can!


Kind regards,

Simon