Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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 |
Hello,
Try to concatenate your tables, see the qwv attached.
Best regards.
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).
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.
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!!
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.