Question Re: - Using date-dimensions-for-reporting
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?
Files:
datelookup.PNG
The same question 
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:
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
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:
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
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
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
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"
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"
no worries Nigel, all's well that ends well!
regards,
David
no worries Nigel, all's well that ends well!
regards,
David
Replies have been locked on this page!