Group By and Having Clause in Qlikview

[remo0017]

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

[sunny_talwar]

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;

[sunny_talwar]

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;

[remo0017]

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.

[sunny_talwar]

No question is dumb ... Read about Preceding Load and you will understand how is it working....