Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Joining and aggregating data on load

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:

personIDpledge_numbergift_numbergift_amtdesg
123781234500123456789
4564556782000987654321
7891291233000123456789
789091245000879546213
123781235500123456789

The pledge table:

personIDpledge_numberpledge_amtdesg
123781000123456789
4564510000987654321
4569810000654987321
7891220000123456789
5 Replies
Not applicable

Re: Joining and aggregating data on load

Hello,

Try to concatenate your tables, see the qwv attached.

Best regards.

Not applicable

Re: Joining and aggregating data on load

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

Not applicable

Re: Joining and aggregating data on load

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.

Not applicable

Re: Joining and aggregating data on load

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

vamshi_1957
Contributor II

Re: Joining and aggregating data on load

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.

Community Browser