Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

6 Replies
jvitantonio
Valued Contributor III

1 fact table twice linking to 1 dimension

Hi,

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

MVP
MVP

Re: 1 fact table twice linking to 1 dimension

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

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?

MVP
MVP

Re: 1 fact table twice linking to 1 dimension

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: 1 fact table twice linking to 1 dimension

Hi Bart,

see file attached.

Hope it helps.

Giampiero

1 fact table twice linking to 1 dimension

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

Community Browser