Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

hencovanee
Contributor II

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

Tags (3)
1 Solution

Accepted Solutions
chriscammers
Contributor III

Link table issue

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;

5 Replies
hencovanee
Contributor II

Link table issue

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

chriscammers
Contributor III

Link table issue

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;

hencovanee
Contributor II

Link table issue

Hi Chris,

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

Cheers,

Henco

hencovanee
Contributor II

Link table issue

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

Link table issue

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

Community Browser