Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Why don't you split your Party table into Sender and Receiver? and then join each table with teh right key...
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
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?
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:
and a list box showing PartyName.
Regards
Jonathan
Hi Bart,
see file attached.
Hope it helps.
Giampiero
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