Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Fill Records for Link Table

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

1 Solution

Accepted Solutions
Not applicable

Re: Fill Records for Link Table

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

2 Replies
Not applicable

Re: Fill Records for Link Table

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

Not applicable

Re: Fill Records for Link Table

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.

Community Browser