Joining and aggregating data on load

[#1]

I'm pretty new to Qlikview and am having problems getting my brain around the Qlikview way to do things, particularly with regard to joining and aggregating data in the load script.

I work for a nonprofit in the advancement (ie. fundraising section). I've got a couple tables, one which shows pledges people have made, and another which shows gifts. Some gifts are made towards pledges some are not.

I'd like to come up with a table which was one line per commitment, either a pledge together with its payments or just an outright gift (later on, I plan to further aggregate these commitments by the project towards which they're made). But while I've got unique keys (gift_number and pledge_numbers) for each transaction, I'm having troubles getting the two tables to play nicely which each other so that I end up with a table like:

• personID
• pledge_number
• pledge_amount
• gift_number (if it's only one gift)
• sum(gift_amt)
• designation_code

Here's my sample data:

The gifts table:

personID	pledge_number	gift_number	gift_amt	desg
123	78	1234	500	123456789
456	45	5678	2000	987654321
789	12	9123	3000	123456789
789	0	9124	5000	879546213
123	78	1235	500	123456789

The pledge table:

personID	pledge_number	pledge_amt	desg
123	78	1000	123456789
456	45	10000	987654321
456	98	10000	654987321
789	12	20000	123456789

[#2]

Hello,

Try to concatenate your tables, see the qwv attached.

Best regards.

[#3]

Oh snap. I'm still on the Personal Edition....(trying to build something cool enough to get approval to buy at least a license or two).

[#4]

Hello,

Write the follows in your script editor and make a table (Dimension: personID, pledge_number.   Expresions: Sum(gift_amt), Sum(pledge_amt)).

Fact:

//Gift:

LOAD * INLINE [

    personID, pledge_number, gift_number, gift_amt, desg

    123, 78, 1234, 500, 123456789

    456, 45, 5678, 2000, 987654321

    789, 12, 9123, 3000, 123456789

    789, 0, 9124, 5000, 879546213

    123, 78, 1235, 500, 123456789

];

Concatenate(Fact)

//Pledge:

LOAD * INLINE [

    personID, pledge_number, pledge_amt, desg

    123, 78, 1000, 123456789

    456, 45, 10000, 987654321

    456, 98, 10000, 654987321

    789, 12, 20000, 123456789

];

Best regards.

[#5]

So push the aggregation into the document rather than in the load script?

I suppose that makes sense--it certainly makes my brain hurt less!!

[Anonymous]

May be even without concatenation also, you can directly create a straight table taking all dimensions and Sum(gift_amt) and sum(pledge_amt) as expressions.

But this will result in formation of synthetic table.