
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Group By and Having Clause in Qlikview
Hi Everyone,
May I know how can i implement the group by and having clause on Qlikview. I have a situation where i need to identify the number of records that share same resource with different rankings.
Ranking: 1,2,4,5
EMP_ID
Room_No
Now I want to identify all the EMP_ID's that share same common Room_No,
Example: EMP_ID's with ranking 1 & 5 that share same Room_No
EMP_ID's with ranking 2 & 4 that share same Room_No
select EMP_ID, count(EMP_ID), Room_No
from Table1
where Ranking IN (1,2,4,5)
group by room_no
having count(EMP_ID) > 1;
How do I achieve this on Qlikview or is the above SQL query correct ??

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Unfortunately, there is no direct way to implement Having in QlikView script... you can use Preceding load to do this (a nested query)
Table:
LOAD *
Where Count > 1;
Load EMP_ID,
Count(EMP_ID) as Count,
Room_No
FROM ....
Where Match(Ranking, 1, 2, 4, 5)
Group By Emp_ID, Room_No;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Or this
Table:
LOAD *
Where Count > 1;
Load Count(EMP_ID) as Count,
Room_No
FROM ....
Where Match(Ranking, 1, 2, 4, 5)
Group By Room_No;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
LOAD *
Where Count > 1;
Load Count(EMP_ID) as Count,
Room_No
FROM ....
Where Match(Ranking, 1, 2, 4, 5)
Group By Room_No;
In the above statement how does this work without FROMclause
LOAD *
Where Count > 1;
I am new to Qlikview so please bear with me if its a dumb question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
No question is dumb ... Read about Preceding Load and you will understand how is it working....
