Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
imrasyed
Creator II
Creator II

Help with Intersecting and not Intersecting dataset

Hi Experts. I have a dataset

RName      Pid       Severe

test                1          critical

test               2          High

test              3          Low

test1           1           Critical

test1            3          Medium

test2            4          Critical

test3            6          Critical

Result:

1.Count of Rname with only Critical:

test2            4          Critical

test3            6          Critical

 

 

 

Labels (1)
1 Solution

Accepted Solutions
Kushal_Chawda

@imrasyed  try below

Front end solution

=sum(aggr(if(Severe='Critical',1),RName))

 

Back end solution

 

Data:
LOAD * Inline [
RName,      Pid  ,     Severe

test ,               1 ,         critical

test ,              2  ,        High

test ,             3   ,       Low

test1 ,          1    ,       Critical

test1 ,           3 ,         Medium

test2  ,          4   ,       Critical

test3 ,           6   ,       Critical ];

RName_cnt:
LOAD RName,
     Count(distinct RName) as RName_Cnt
Resident Data
where Severe='Critical'
Group by RName;

Left Join(RName_cnt)
LOAD RName,
     Count(distinct Severe) as Severe_cnt
Resident Data
Group by RName;

Left Join(Data)
LOAD RName,
     1 as Critical_Flag
Resident RName_cnt
where RName_Cnt=Severe_cnt;

DROP Table RName_cnt;

 

then you can use below expression

=count({<Critical_Flag={1}>}RName)

View solution in original post

2 Replies
Kushal_Chawda

@imrasyed  try below

Front end solution

=sum(aggr(if(Severe='Critical',1),RName))

 

Back end solution

 

Data:
LOAD * Inline [
RName,      Pid  ,     Severe

test ,               1 ,         critical

test ,              2  ,        High

test ,             3   ,       Low

test1 ,          1    ,       Critical

test1 ,           3 ,         Medium

test2  ,          4   ,       Critical

test3 ,           6   ,       Critical ];

RName_cnt:
LOAD RName,
     Count(distinct RName) as RName_Cnt
Resident Data
where Severe='Critical'
Group by RName;

Left Join(RName_cnt)
LOAD RName,
     Count(distinct Severe) as Severe_cnt
Resident Data
Group by RName;

Left Join(Data)
LOAD RName,
     1 as Critical_Flag
Resident RName_cnt
where RName_Cnt=Severe_cnt;

DROP Table RName_cnt;

 

then you can use below expression

=count({<Critical_Flag={1}>}RName)
imrasyed
Creator II
Creator II
Author

Thanks a lot Kush.