Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Edit: my goal here is to show the revenue per Salesrep.
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;
Hi Chris,
Thank you very much, you really helped me out here.
Cheers,
Henco
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
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