topic Re: Parent-child table multiplying in aggregate functions in App Development

Parent-child table multiplying in aggregate functions

walkasia — Thu, 16 Nov 2017 19:43:53 GMT

I am new to QlikSense Desktop, but have significant SQL experience.

I have a parent table and a child table. I have numeric values in the parent table that I want to be able to sum and average etc. But when joined to the child table, those numeric values get muliplied by the number of rows in the child table.

Table 1: Household

HouseholdID

HouseholdName

NumberOfChildren

Table 2: Service

HouseholdID

ServiceDate

ServiceType

So for example, I want to display the number of children that received certain types of services each year. But the children should not be double (triple etc) counted. For example, of John and Sally's family received food assistance 4 times this year, or if they received food assistance just once this year, they should count as just 2 children either way.

I can see how sometimes you might want it to multiply, but in our case we do not.

How can I stop the multiplying?

Re: Parent-child table multiplying in aggregate functions

rwunderlich — Thu, 16 Nov 2017 22:09:53 GMT

When you say join, do you mean you joined the tables in the script? Or that you kept then as two separate tables linked by HouseholdID. If you keep them as two tables (recommended) there should be no double counting.

Did you create one or two tables?

-Rob

Re: Parent-child table multiplying in aggregate functions

luismadriz — Thu, 16 Nov 2017 22:55:06 GMT

Agree with Rob.

I'm new to Qlik too and I actually found that to be quite amazing of Qlik Sense. In regular SQL it would have been multiplied but not with Qlik associations! I still struggle getting my head around it...

Gwendly please put some simple sample data together and keep us posted. I'd like to see that issue you're having

Cheers,

Luis

Re: Parent-child table multiplying in aggregate functions

walkasia — Fri, 17 Nov 2017 19:44:10 GMT

It seems my problem is somehow in the source data in our database. If I pull data from January 2014 onward (which is 30,000 activities), the numbers are correct. When I pull data prior to January 2014, I am getting crazy numbers that I can't understand. Because 2014 onward is clean, I think my QlikSense table configuration is fine. I am still researching.

Because I am so new to Qlik, your answers were still a huge help. I was running around in circles trying different was to join and not join. (in the SQL, in the Load script, linking in the data manager). Your answers helped me know what my goal configuration SHOULD be, and which it now is... they are loaded as two different tables in the Load Script, with a commonly named key field, resulting in a natural linking in Qlik.

If I am able to find out why I am having trouble with 2013 and earlier data, I will let you know.

Thanks!