on 04-12-2016 9:12 PM
I have 2 table with the below dates. SQL Server is the database. Table A 2016-03-31 2016-02-29 Table B 2016-03-1 2016-02-1 I have to join these 2 tables to get data for each month. However Inner join would fail and trying to extract month & year and joining in SQL, which is working fine. Table A.MONTH=Table B.MONTH AND Table A.YR=Table B.YR But how would i do this join in the universe level?
This is a design question rather than a technical question. Check with your DBA to decide on the best way.
My take would be to create a Date_Dim table.
why?
How?
I've uploaded the data in CSV format here for you to play around. (Dates are from 01-Sep-2015 to 30-March-2016)
After creating the time_dim table (or date dim) setup your universe (unx or unv - it doesn't matter).
Make sure that the outer join is always on the Time_Dim table.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Cristina,
How about creating Calculated Columns for Month and Year in those tables and then join on those newly calculated columns as Table A.Month = Table B.Month and Table A.Year = Table B.Year?
You can use Year() and Month() function to get the Month and Year number, its available by default in IDT for some databases, check out if it works for the type of database you are using.
This can help to you create Calculated Columns using Date value:
Information design tool 4.0: Create a calculated column using a date value
Let us know if this helps and/or you have any questions/issues.
Update: If you've used Universe Design Tool (UDT) to create the Universe, then you'll have to create a Derived table.
Thanks,
Mahboob Mohammed
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
10 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.