topic Re: Joining and aggregating data on load in QlikView

Joining and aggregating data on load

Mon, 16 Dec 2013 21:44:14 GMT

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

Re: Joining and aggregating data on load

Mon, 16 Dec 2013 21:57:38 GMT

Hello,

Try to concatenate your tables, see the qwv attached.

Best regards.

Re: Joining and aggregating data on load

Mon, 16 Dec 2013 22:10:15 GMT

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).

Re: Joining and aggregating data on load

Mon, 16 Dec 2013 22:17:39 GMT

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.

Re: Joining and aggregating data on load

Mon, 16 Dec 2013 22:23:26 GMT

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!!

Re: Joining and aggregating data on load

Anonymous — Tue, 17 Dec 2013 20:22:28 GMT

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.