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,

       

      Bart

        • 1 fact table twice linking to 1 dimension
          j i

          Hi,

           

          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

            Bart

             

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

             

            Party_Map:

            Mapping Load * From Party;

             

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

             

            Data:

            LOAD TransactionID,

                 Date,

                 Sender_ID,

                 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

            Jonathan

              • 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:

                 

                Sender_IDReceiver_IDTransaction_ID
                AB1
                AC2
                AB3
                BD4
                BD5
                BC6

                 

                 

                Party_IDName
                ACompany_A
                BCompany_B
                CCompany_C
                DCompany_D

                 

                 

                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

                    Bart

                     

                    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.

                     

                    Regards

                    Jonathan

                • Re: 1 fact table twice linking to 1 dimension
                  Giampiero Cina

                  Hi Bart,

                  see file attached.

                  Hope it helps.

                  Giampiero

                  • 1 fact table twice linking to 1 dimension
                    Rob Wunderlich

                    Load Party twice (concatenated)

                     

                    Party:

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

                    SQL SELECT * FROM PartyTable;

                    CONCATENATE

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

                    SQL SELECT * FROM PartyTable;

                     

                    -Rob