Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Link table issue

Hi all,

I am struggling with a (for me) difficult issue. It is about a link table which I use in my document.

To explain the issue, I have made a very simple and basic example. This demo file is attached to this issue, so I hope someone can explain to me the way it works.

The problem is:

A salesrep is linked to a customer, and a customer has transactions. To bring this (and much more tables, thats why I use a link table here) together I use a link table. In my opinion all the joins are right, but when I make a straight table which gives me: Customername, Salesrep and Transactionamount, I get confused because there is no salesrep linked to the customer.

Can anyone please show me, what do I do wrong?

Thanks in advance.

Kind regards,

Henco

1 Solution

Accepted Solutions
chriscammers
Partner - Specialist
Partner - Specialist

When you are adding the RepID to the linkTemp you are concatenating. This does not create an association with the  transactions. If you look at your link table data you will see what I mean. I suggest using a left join to the linked table.

Like this...

LinkTemp:

LOAD DISTINCT

GL_ID                              as %GL_ID,

Customer_ID                    as %CUSTOMER_ID,

Transdate                    as DateKey

RESIDENT TRANSACTION;

//CONCATENATE

Left Join(LinkTemp) //This is the line I added.

LOAD DISTINCT

ID                                        as %CUSTOMER_ID,

RepID                              as VertegenwoordigerKey

RESIDENT CUSTOMER;

View solution in original post

5 Replies
Anonymous
Not applicable
Author

Edit: my goal here is to show the revenue per Salesrep.

chriscammers
Partner - Specialist
Partner - Specialist

When you are adding the RepID to the linkTemp you are concatenating. This does not create an association with the  transactions. If you look at your link table data you will see what I mean. I suggest using a left join to the linked table.

Like this...

LinkTemp:

LOAD DISTINCT

GL_ID                              as %GL_ID,

Customer_ID                    as %CUSTOMER_ID,

Transdate                    as DateKey

RESIDENT TRANSACTION;

//CONCATENATE

Left Join(LinkTemp) //This is the line I added.

LOAD DISTINCT

ID                                        as %CUSTOMER_ID,

RepID                              as VertegenwoordigerKey

RESIDENT CUSTOMER;

Anonymous
Not applicable
Author

Hi Chris,

Thank you very much, you really helped me out here.

Cheers,

Henco

Anonymous
Not applicable
Author

Hi Chris,

It's been a while ago, but I still have an issue with my link table. The issue I described at first is solved, thanks to your answer, but what I miss right now are the customers who don't have any transactions. What I would like to do is show all the customers per salesrep, by using the Link table. Due to the left join, I can't get all the customers linked to the salesrep because there are no transactions. I strongly hope you do have any options for me. Thanks in advance.

Kind regards,

Henco

Nicole-Smith

Hi Henco,

Try using an outer join instead of the left join.  This should keep all of the records from both tables so that you aren't missing any data.

Nicole