topic Link table issue in QlikView

Link table issue

Anonymous — Fri, 06 Jan 2012 14:09:00 GMT

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

Link table issue

Anonymous — Fri, 06 Jan 2012 14:24:54 GMT

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

Link table issue

chriscammers — Fri, 06 Jan 2012 14:50:50 GMT

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;

Link table issue

Anonymous — Sat, 07 Jan 2012 15:38:48 GMT

Hi Chris,

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

Cheers,

Henco

Link table issue

Anonymous — Tue, 01 May 2012 08:09:26 GMT

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

Nicole-Smith — Tue, 01 May 2012 12:37:19 GMT

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