Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
isciberras
Creator
Creator

Group by and sum not working properly

Hi Everyone,

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


I have a table which looks like this:

UserIssue Type1Date
1101/01/2017
1001/01/2017
1102/01/2017
2102/01/2017
2102/01/2017
2103/01/2017
3003/01/2017
4104/01/2017
4104/01/2017
4004/01/2017
4105/01/2017

The aim is to have a result table like below:

User
sum Issue Type1
12
23
30
42

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:

Usersum Issue Type1
11
12
21
22
23

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

4 Replies
sunny_talwar

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, 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

];


AggregatedTable:

LOAD User,

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

Resident Table

Group By User;


Capture.PNG

swuehl
MVP
MVP

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;

isciberras
Creator
Creator
Author

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

isciberras
Creator
Creator
Author

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