topic Re: 1 fact table twice linking to 1 dimension in QlikView

1 fact table twice linking to 1 dimension

Tue, 05 Jun 2012 10:11:15 GMT

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.

All help is well appreciated!

Kind regards,

Bart

1 fact table twice linking to 1 dimension

jvitantonio — Tue, 05 Jun 2012 10:28:27 GMT

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

jonathandienst — Tue, 05 Jun 2012 10:34:49 GMT

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

Tue, 05 Jun 2012 12:18:22 GMT

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_ID	Receiver_ID	Transaction_ID
A	B	1
A	C	2
A	B	3
B	D	4
B	D	5
B	C	6

Party_ID	Name
A	Company_A
B	Company_B
C	Company_C
D	Company_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

jonathandienst — Tue, 05 Jun 2012 13:12:04 GMT

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

Wed, 06 Jun 2012 13:53:18 GMT

Hi Bart,

see file attached.

Hope it helps.

Giampiero

1 fact table twice linking to 1 dimension

rwunderlich — Wed, 06 Jun 2012 18:47:38 GMT

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