Role Playing Dimensions (not dates)

[#1]

I am new to Qlikview and came into a problem I can't seem to be able to solve.

I have a dimension of Banks (with the Bank code, the name and some other information). One of my fact tables is a table of account transfers from one bank to another. In this table I have one column for the Bank that transfers the account and one column for the Bank that receives the account. One Bank can play one role in one transfer and another in different transaction.

If I try to create two key fields in the dimension table with Tranfering_Bank and Receiving_Bank, I get an automatic synthetic key pointing to the fact table (not the desired effect). If I create two Bank dimensions (one for Transfering Bank and another for Receiving Bank) then I cannot filter by Bank and get aggregation information for each bank (like the net transfers - received minus transfers ceded).

Thank you for anyone that can help.

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

I prefer to use two dimension tables with the same values for each of the possible values in the fact table, as I would do if we were talking about dates (same Order, Shipment Date, Requested Date, linked to multiple different calendars). And play with data islands and set analysis P() element function (also called indirect set analysis) to display charts.

Quite a simple example but hope it helps.

Miguel

[#3]

Thank you for your help. I am reading about Set Analysis and trying it out. Could you post here your example? I am not at the office and I cant open other's documents in my laptop.

Thanks again.

[#4]

Thanks again. I used a Bank table as an island and just needed an count(if (ISLAND_BANK=TRANSFERING_BANK), TRANSFER_KEY)) function. I repeated this for the receiving Bank and lastly subtracted column(1) from column(2)

Good I didnt need Set Analysis because I was having trouble with it.

Daniel