topic Re: Group by and sum not working properly in QlikView

Group by and sum not working properly

isciberras — Wed, 25 Nov 2020 16:16:04 GMT

Hi Everyone,

I'm having trouble using sum and group by in QLikview.

I have a table which looks like this:

User	Issue Type1	Date
1	1	01/01/2017
1	0	01/01/2017
1	1	02/01/2017
2	1	02/01/2017
2	1	02/01/2017
2	1	03/01/2017
3	0	03/01/2017
4	1	04/01/2017
4	1	04/01/2017
4	0	04/01/2017
4	1	05/01/2017

The aim is to have a result table like below:

User

sum Issue Type1

The code I am using is:

Pattern:

load

sum (Issue Type1) as [sum Issue Type1]

Resident Final

Where [Issue Type1] =1

Group by User;

___

However an example of the result I am getting is:

User	sum Issue Type1
1	1
1	2
2	1
2	2
2	3

Can anyone help me out please? as I ran out of ideas. I have tried using count and or distinct however these did not work

Thanks,

Isaac

Re: Group by and sum not working properly

sunny_talwar — Tue, 16 Jan 2018 15:38:55 GMT

Seems to be working

Table:

LOAD * INLINE [

User, Issue Type1, Date

1, 1, 01/01/2017

1, 0, 01/01/2017

1, 1, 02/01/2017

2, 1, 02/01/2017

2, 1, 03/01/2017

3, 0, 03/01/2017

4, 1, 04/01/2017

4, 0, 04/01/2017

4, 1, 05/01/2017

];

AggregatedTable:

LOAD User,

Sum([Issue Type1]) as [Sum Of Issue Type 1]

Resident Table

Group By User;

Re: Group by and sum not working properly

swuehl — Tue, 16 Jan 2018 15:40:39 GMT

Shouldn't you add User as dimensional field in your Group by LOAD?

Pattern:

load

User,

sum (Issue Type1) as [sum Issue Type1]

Resident Final

Where [Issue Type1] =1

Group by User;

Re: Group by and sum not working properly

isciberras — Tue, 16 Jan 2018 15:45:12 GMT

Thank you sunny,

You are completely right it does work I'm so embarrassed my issue was that I wasn't linking "User" between tables and this is why I had such an odd result. My appologies for taking up your time on this, thank you for all your help.

Isaac

Re: Group by and sum not working properly

isciberras — Tue, 16 Jan 2018 15:46:10 GMT

Thank you Stefan,

Life saver too, not linking User was the problem - I feel so stupid. Thank you very much for taking you time to help me out you were a big help

Isaac