6 Replies Latest reply: Jun 6, 2012 2:47 PM by Rob Wunderlich RSS

    1 fact table twice linking to 1 dimension

      This is the situation:


      I got 1 table containing transactions between parties.

      One party being the sender of the transactions, the other party being the receiver of the transaction.


      The senders and receivers of transactions are stored in one table, called parties.


      using Qlikview I want to get a view on the following kinds of information:

      - to who (receivers) is a sender sending transactions?

      - from who (senders) is a receiver receiving transactions?


      If these were the only questions to be answered, I could easily load the parties twice, but as a party can be a sender as well as a receiver in my model, I want to get both questions above answered in one time selecting only once the name of the party.


      In a simplified relational model, this is very simple, but I cannot reproduce this in qlikview as I would have to deal with circular references.

      And in this case I have no idea what could be the ideal solution.Transaction_party.png


      All help is well appreciated!


      Kind regards,



        • 1 fact table twice linking to 1 dimension
          Juan Vitantonio



          Why don't you split your Party table into Sender and Receiver? and then join each table with teh right key...

          • Re: 1 fact table twice linking to 1 dimension
            Jonathan Dienst



            The way to handle this is to use mapping rather than a join. First do a mapping load of the Party table:



            Mapping Load * From Party;


            Then use ApplyMap to load the names from the IDs directly into the main table:



            LOAD TransactionID,



                 Applymap('Party_Map', Sender_ID) As Sender_Name,

                 Applymap('Party_Map', Receiver_ID) As Receiver_Name

            FROM Transaction;


            The mapping table will be automatically dropped at the end of the load, so there are no joins, loops or synthetic keys.


            Hope that helps


              • 1 fact table twice linking to 1 dimension

                there are two reasons why I think the applymap functionality won't work:

                1. This is a simplified representation of my Party dimension. In real it contains more attributes.

                2. If I do this, then it won't be possible to have one view in which I can see

                     - to who a party is sending transactions

                     - from who that same party is receiving transactions.


                Suppose I have following data:








                At the end, if I select 'Company_B' in my dashboard, I would like to see that:

                - Company_B has sent 2 transactions to Company_D and 1 transaction to Company_C

                - Company_B has received 2 transactions from Company_A


                If I perform the applymap functionality, then I would have to search twice.

                Once selecting Company_B as the sender, once selecting Company_B as the receiver.


                Or am I wrong?

                  • Re: 1 fact table twice linking to 1 dimension
                    Jonathan Dienst



                    I you need to bring in more attributes, you could use applymap again (if there are say 2 or 3 more). If there are too many, then you would have to create two Party tables, one for Sender and one for Receiver.


                    For your "2 way" query - you can build a data island (ie not linked to anything) containing fields Party_ID, PartyName; and then create a straight table object that shows two expressions:


                    • Received From: one using set expression like {<Receiver_ID=P(Party_ID)>} ..ie any sender, receiver based on Party_ID selection)
                    • Sent By: nd one showing {<Sender_ID=P(Party_ID)>} .. ie any receiver (sender will be based on Party_ID selection)


                    and a list box showing PartyName.




                • Re: 1 fact table twice linking to 1 dimension

                  Hi Bart,

                  see file attached.

                  Hope it helps.


                  • 1 fact table twice linking to 1 dimension
                    Rob Wunderlich

                    Load Party twice (concatenated)



                    LOAD fieldlist, Party_ID as Sender_ID, 'Sender' as PartyType;

                    SQL SELECT * FROM PartyTable;


                    LOAD fieldlist, Party_ID as Receiver_ID, 'Receiver' as PartyType;

                    SQL SELECT * FROM PartyTable;