5 Replies Latest reply: May 1, 2012 8:37 AM by Nicole Smith RSS

    Link table issue

    Henco van Ee

      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
          Henco van Ee

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

            • Link table issue
              Chris Cammers

              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
                  Henco van Ee

                  Hi Chris,

                   

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

                   

                  Cheers,

                  Henco

                  • Link table issue
                    Henco van Ee

                    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