Question Re: - Using date-dimensions-for-reporting

Nigel Smith shared this question 2 years ago
Answered

Hi - Ive been reading the article

https://community.yellowfinbi.com/knowledge-base/article/how-do-i-use-date-dimensions-for-reporting


In our database (postgres) we have a timestamp field but when I try and create a join in the view between this table and the "datelookup" table the timestampfield isn't visible.

I assume this is because the field in my table is "timestamp" and the field in the datelookup table is "date" only?


Do you know any way around this, or offer any advice?

Comments (4)

photo
1

Hi Nigel,

yes you are correct - you can only join between columns of the same data type. One way to join your callrecord table to your datelookup table would be to cast callrecord.starttimestamp to the DATE data type. And the way to do that is to use a Virtual Table instead of dragging in the actual table. So the steps would be something like:


1) Drag in the datelookup table

2) Don't drag in the callrecord table, instead, drag in a Virtual Table and call it callrecord_new

3) Open up the Virtual Table's SQL editor and enter in:


   SELECT parentcall, partition, rdnis, redirected, (etc etc), CAST(starttimestamp AS DATE) as starttimestamp_DATE

   FROM callrecord
and then validate and save the Virtual Table.

4) Then you should be able to join between datelookup.daydate and callrecord_new.starttimestamp_DATE.


I hope that helps you meet your reporting requirements, if it doesn't please let me know what the issue with it is and I'll try and give some more ideas.


regards,

David

photo
1

Hi Nigel,

just cleaning up my worklist and noticed there's been no response to here for over a month, so I'm just wondering how you got on this, and whether my suggestion of using the Virtual Table helped you, and hence whether this ticket may be closed now.

regards,

David

photo
1

Hi David,

Yes this did help and this ticket can be closed. Thanks for your help and apologies for not getting back to you.

Regards

Nigel

From: Yellowfin Support [mailto:support@yellowfin.bi]

Sent: 12 September 2017 06:48

To: Nigel Smith

Subject: New Comment in "Question Re: - Using date-dimensions-for-reporting"

photo
1

no worries Nigel, all's well that ends well!

regards,

David