Compare different columns with same values in tables

[#1]

HiGuys,

I am having a question and I absolutelly no clue how to start to work on this challenge. Maybe you can give me a hint into the right direction.

I have the following table:

Sen der	Receiver	Amount
A	X	18
A	Y	6
A	Z	14
K	A	43
L	A	2
M	A	5

I do a lot of analysis based on sender or receiver and sum up the totals. Now I would like to see the "balance" for each sender or receiver. Meaning:

I would need a listbox where I can find all (distinct) sender and receiver (Example here: A, K, L, M, X, Y, Z). By choosing one of them i'd like to see the balance. As an example: If i choose A, i would like to see something like:

Sent	Received
A
38	50

Thanks for any hint how do load the data right, or how to set an expression in a table chart to calculate the right coice.

BR

Mathias

[swuehl]

I think one approach might be to transform your table into a table with columns Person, Action, Amount, like

LOAD Sender as Person,

'Sent' as Action,

Amount

FROM Table;

LOAD Receiver as Person,

'Received' as Action,

Amount

FROM Table;

Then you can select from Person and create a chart grouped by (i.e. dimension) Action with expression sum(Amount).

[masha-ecraft]

Reorganise your data so that you have sender and receiver in the same column name like 'Sender/Receiver' and another column for transaction type that would have a value 'Sent' or 'Received'.

Then you could do all analysis based on the transaction type and have a chart for Sender/Receiver with Sent and Received totals calculated like that: sum({<TransactionType = {'Sent'}>} Amount)

[mphekin12]

Try something like this.

[#5]

Hi Gents,

all of you did load the data somehow twice. That's not really an option for me, because we are talking about roughly 65 mio. records. So loading twice, would mean double the amount.

From Masha A answer i took the part with the expression. Any chance to replace the {'sent'} (the filter key for the transaction type) with an chosen element out of a listbox?

Thanks,
Mathias

[mphekin12]

Mathias,

In my example I am only loading the data once then I'm doing a couple of resident loads.  Since the data is in memory after the first load, I would not think that it would take as long for the resident loads to complete.  You could try to load a smaller sample of your data and see.

Would anyone know the time savings of using a resident load?

[swuehl · Accepted solution]

I would give it a try using the doubled-table-load approach, it should compress quite well.

Alternatively, you could just create a data island for Person:

Table1:

LOAD * FROM

[http://community.qlik.com/message/221693]

(html, codepage is 1252, embedded labels);

Persons:

LOAD distinct [Sen der] as Person Resident Table1;

LOAD distinct [Receiver] as Person Resident Table1;

Then create a chart with dimension Person and as expressions:

=sum({<Receiver=Person>} Amount)

=sum({<[Sen der]=Person>} Amount)

for received resp. sent amount.

This will only work correctly if one Person is selected.

Or replace the above expressions with

=sum(if(Receiver=Person, Amount))

=sum(if([Sen der]=Person, Amount))

[masha-ecraft]

Mathias,

If you want avoid double records in your main table, you can create a key field in your main table:

MainTable:

LOAD

*,

Receiver & '/' & Sender as Key

and then create an additional helper table with Sender and Receiver loaded into the same column:

Person:

LOAD distinct

Key,

Sender as Person,

'Sent' as TransactionType

Resident MainTable;

concatenate (MainTable)

LOAD distinct

Key,

Receiver as Person,

'Received' as TransactionType

Resident MainTable;

Then you can make a chart with Person as dimension and sum({<TransactionType = {'Sent'}>} Amount) as an expression.

/Masha

[#9]

Gents, thank you so much for helping me out. All answers have been useful and I learned a lot based on this. Finally swuehls reply was exactly what I was looking for.

Thanks to all of you! Great help!