5 Replies Latest reply: Dec 17, 2013 3:22 PM by Vamshi Tataipelli RSS

    Joining and aggregating data on load

    Jake Tolbert

      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