topic Re: Fill Records for Link Table in QlikView

Fill Records for Link Table

Mon, 16 Dec 2013 15:20:24 GMT

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

Re: Fill Records for Link Table

Mon, 16 Dec 2013 15:31:21 GMT

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

Re: Fill Records for Link Table

Tue, 17 Dec 2013 12:28:36 GMT

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.