How to handle two fields when act as the same key

[rdomenoqv]

Hi to everybody:

I have this table of transactions (thousands of them), with this typical register design:

…

…

bank_of_credit

…

bank_of_debit

…

amount_of_transaction

…

…rest of fields…

Despite the thousands of transactions, the number of real banks is near 70 (this is the whole system), and ANY bank may act as CREDIT in a lot of transactiones, and as DEBIT in other lot of other transactions, including that the same bank may act as CREDIT and DEBIT in the same transaction.

As you can see, I get the number of CREDITS and DEBITS (and their amounts) for EVERY bank, but, in TWO different objects (two separated chart/straight)

dimension:                dimension:

   bank_of_credit            bank_of_debit

expressions:              expressions:

   count(id)                 count(id)

   sum(amount)               sum(amount)

I would like to get this:

This chart/straight was made with TWO TABLES (splitting the original table, and creating one for DEBITS and other for CREDITS, each one with the same key: bank number); this was the only solution I could find (according to my experience, of course...) the problem with this two-table-model, is that I had to "isolate" from the rest, and, if I continue that way, can't apply lot of filters that are already built and working ok in other dashboards.

I would like to get this "unified" graphic in ONE object (one straight table), but with ONE TABLE (the original table of transactions).

Have tried lot of ways, but seems difficult to me, to handle two fields which are the same, but act in different ways.

Any suggestions? Thanks a lot

[Anil_Babu_Samineni]

I would recommend to use Key between your fact and Transaction table. So, You can use that Key to filter and it is optimized. So, Try that way and let use know

[rdomenoqv]

Hi Anil:

Thanks for your fast answer, but, would you please add any more explanation? I would appreciate that (because cant catch your suggestion) Thanks again

[Anil_Babu_Samineni]

No, When we have tables like KPI's, Fact, Dimension and Transaction Tables ...

Some case, If we are linking to Calendar table from Fact to Calendar using Primary key of Datefield then there is some issue to map if we have more than one date field. So, I would suggest to club into single datefield and concatenate them into one.

Finally, Here if we create Key using Autonumber(), AutonumberHash128(), AutonumberHash256() for 128/256 bits or you can simple composite to each adding and associate to problem. In fact, your data model quiet different. Can you help us to understand the issue where and which you want to maintain same key?

[rdomenoqv]

As I said before, to now, I have built (almost) 70 objects in 12 sheets, in order to deploy and show the universe of these transactions; because of this, I (reading your first answer) thought that would be hard to change all the previous work made.

However, also thought that if this case may be solved, another one will appear soon, so, really is time to redefine or recheck or re-tune the whole data model.

In the original design, I considered all kind of suggestions from the books, and the "good practices" to build proper data models, but considering that QV is fast an easy to develop in order to get my first outputs, surely I was "leaving a side" that good practices in some moments.

For that reason, in these days, I will start form table-viewer and think what need to be rewritten.

Anyway, thanks for your answers; perhaps didn't solve explicity that problem, but surely helped to solve a major  kind of problem that is not always visible.

Thanks again