topic Re: Basic join without disturbing sum in QlikView

Basic join without disturbing sum

Wed, 11 Sep 2013 13:00:43 GMT

I´m stuck with a newbie problem.

I have two table´s with data that conserns payments. In the first, Table A is all the info about the payment, dates and amounts. The second contains social securitynumber. Both tables have the field PaymentNR in common and a payment often concerns more then one person...

I would like to merge the SocialSecurityNr into table A. If I do a left join on PaymentNR the amounts get´s wrong for those paymentNR that invovles more then one person/socialsercurityNR. If I do a concat I only get one of the person for each payment that concerns more then one person.

Can someone hint me how to do. I´d tryed to map but did´t succed, also here in the case were one payment conserned more then one person.

Re: Basic join without disturbing sum

Wed, 11 Sep 2013 13:09:41 GMT

Hey,

use the AGGR() function

aggr(sum(YOURFIELD),YOURDIMENSION))

YOURFIELD is probably Payment_amount or something like that

YOURDIMENSION is PaymentNR.

Cheers

Juan Pedro

Re: Basic join without disturbing sum

orital81 — Wed, 11 Sep 2013 13:14:27 GMT

Hi Hofstedt

It seems like a Many to Many join

I suggest one of the following:

1. Dont write nothing in the join (no left, no right), it will join it as full outer join.

2. Alternatively, use a Key Table method.

For more info - go to here

Re: Basic join without disturbing sum

Wed, 11 Sep 2013 13:20:53 GMT

With only join it gets wrong. But with %Keytable it get right. I guess I have to have it in two table´s. Thanks for the paper, will read it.