Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ??
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;
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;
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.
No question is dumb ... Read about Preceding Load and you will understand how is it working....