topic Re: Group By and Having Clause in Qlikview in QlikView

Group By and Having Clause in Qlikview

remo0017 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Group By and Having Clause in Qlikview

sunny_talwar — Tue, 07 Nov 2017 13:49:48 GMT

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;

Re: Group By and Having Clause in Qlikview

sunny_talwar — Tue, 07 Nov 2017 13:50:07 GMT

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;

Re: Group By and Having Clause in Qlikview

remo0017 — Tue, 07 Nov 2017 14:00:57 GMT

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.

Re: Group By and Having Clause in Qlikview

sunny_talwar — Tue, 07 Nov 2017 14:03:59 GMT

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