Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am currently creating a data model with a Link Table. In this data I want to be able to link to dimension tables with each other.
I have created two tables one with the distinct values that I want to create records for in the Master Link Table and one table with the dates that I want to create records for.
It looks something like this.
Company Link Table:
Company Number, Sales Person ID
Company 1, Salesman 1
Company 1, Salesman 2
Company 2, Salesman 1
………
Company 30, Salesman 15
Date Range:
Date
2014-01-01,
2014-02-01,
…….
2015-01-01
Now I want to create one table that has all possible values for the distinct values of Company Link Table and Date Range.
Link Table:
Company Number, Sales Person ID, Date
Company 1, Salesman 1, 2014-01-01
Company 1, Salesman 2, 2014-01-01
Company 1, Salesman 1, 2014-02-01
Company 1, Salesman 2, 2014-02-01
Company 2, Salesman 1, 2014-01-01
………..
Company 30, Salesman 15, 2015-01-01
I don’t know how to make the last step where I join the two table together and creates the records that are missing. I can’t use a join or concatenate since I don’t have values that are shared in both tables.
Happy for any help.
//Fredrik
Just simply do the cross join between the tables. Please fin;d the sample like below:
MasterLinkTable:
LOAD * INLINE [
Company Number, Sales Person ID
Company 1, Salesman 1
Company 1, Salesman 2
Company 2, Salesman 1
Company 2, Salesman 2
] ;
Join (MasterLinkTable)
LOAD * INLINE [
DATE
2014-01-01
2014-02-01
];
Your final table look like below as you required:
Company Number, Sales Person ID, DATE
Company 1, Salesman 1 , 2014-01-01
Company 1, Salesman 1 , 2014-02-01
Company 1, Salesman 2 , 2014-01-01
Company 1, Salesman 2 , 2014-02-01
Company 2, Salesman 1 , 2014-01-01
Company 2, Salesman 1 , 2014-02-01
Company 2, Salesman 2 , 2014-01-01
Company 2, Salesman 2 , 2014-02-01
Just simply do the cross join between the tables. Please fin;d the sample like below:
MasterLinkTable:
LOAD * INLINE [
Company Number, Sales Person ID
Company 1, Salesman 1
Company 1, Salesman 2
Company 2, Salesman 1
Company 2, Salesman 2
] ;
Join (MasterLinkTable)
LOAD * INLINE [
DATE
2014-01-01
2014-02-01
];
Your final table look like below as you required:
Company Number, Sales Person ID, DATE
Company 1, Salesman 1 , 2014-01-01
Company 1, Salesman 1 , 2014-02-01
Company 1, Salesman 2 , 2014-01-01
Company 1, Salesman 2 , 2014-02-01
Company 2, Salesman 1 , 2014-01-01
Company 2, Salesman 1 , 2014-02-01
Company 2, Salesman 2 , 2014-01-01
Company 2, Salesman 2 , 2014-02-01
Cool, it was that easy. I knew I wanted that table as the result. I didnt know that it was that easy to just use the normal join. I had problem trying to use the joins that it didnt create the records where I didnt have value.