Here is situation I need to resolve.
I have a table with records (transactional records), some records have quantity which is negative (because someone sold something) and some have positive value (because someone bought something). They always come in pairs, I have one transaction ID which will have two rows in my table.
Now, I am trying to make table where I will be able to easily see direction of sales (who is selling to whom), so if my database look like this:
| TransactionID | Value | CustomerID |
|---|
| 1 | 10 | 100 |
| 1 | -10 | 105 |
| 2 | -14 | 107 |
| 2 | 14 | 105 |
| 3 | 99 | 122 |
| 3 | -99 | 100 |
my resulting table chart should look like this:
| TransactionID | Source | Target |
|---|
| 1 | 105 | 100 |
| 2 | 107 | 105 |
| 3 | 100 | 122 |
My table dimension is TransactionID and my expressions are Source and Target, and in this collumns I have to return CustomerID, if the collumn is Source, I have to return CustomerID from the database rows with my TransactionID where Value is negative, and Target should be CustomerID where Value is positive.
Any idea on how to do it? (I guess it is something simple and obvious, but my brain just halted and refuse to cooperate)
