Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Basic join without disturbing sum

Hi

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. 

3 Replies
Highlighted
Not applicable

Re: Basic join without disturbing sum

Hey,

use the AGGR() function

aggr(sum(YOURFIELD),YOURDIMENSION))

YOURFIELD is probably Payment_amount or something like that

YOURDIMENSION is PaymentNR.

Cheers

Juan Pedro

Highlighted
Partner
Partner

Re: Basic join without disturbing sum

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

Highlighted
Not applicable

Re: Basic join without disturbing sum

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.