Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
| sum Issue Type1 | |
---|---|---|
1 | 2 | |
2 | 3 | |
3 | 0 | |
4 | 2 |
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
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;
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;
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;
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
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