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: 
Sandip
Contributor II
Contributor II

How can get the data

Dear Team,

I have solution with this 

age contact
19 cellular
20 telephone
20 cellular
21 telephone
21 cellular
21 unknown
22 telephone
22 cellular
23 telephone
23 unknown
23 cellular
24 telephone
24 cellular
24 unknown

 

but I want Which age are contain all the three contact like cellular,telephone and unknown.

Ans -- Age like 21,23 and 24

age contact
21 telephone
21 cellular
21 unknown
23 telephone
23 unknown
23 cellular
24 telephone
24 cellular
24 unknown

 

We don't want those age who are not all contact (Ex. Age 19,20,22) .

 

Team Please help me.

Thank You!

Regards 

Sandip

 

1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

Try below in Chart

=Sum({<age={"=count({<contact={'cellular','telephone','unknown'}>}distinct contact)=3"}>}balance)

 

Or

If you need to completely remove the data during loading

temp:
LOAD * INLINE [
age,contact
19,cellular
20,telephone
20,cellular
21,telephone
21,cellular
21,unknown
22,telephone
22,cellular
23,telephone
23,unknown
23,cellular
24,telephone
24,cellular
24,unknown
];

inner join (temp)
Load age
Where countContact=3;
LOAD age
, count(distinct contact) as countContact
Resident temp
Group By age
;

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

5 Replies
eddie_wagt
Partner - Creator III
Partner - Creator III

Hello @Sandip ,

Do you need to calculate it in the backend (loadscript) or frontend (set analysis)? In the frontend you could use this expression: 

 

=count ({<age = P ({<contact={"telephone"}>} age ) > * <age = P ({<contact={"cellular"}>} age ) > * <age = P ({<contact={"unknown"}>} age ) >} age )

 

This set analysis can you read as follows:

Give me the count of age where the contact is telephone AND cellular AND unknown. This is called an intersection. You could read more of this in this good document: https://community.qlik.com/t5/Knowledge/Hands-on-Set-Analysis-from-the-ground-up/ta-p/1799118?attach... by @Dalton_Ruer .

If you need to calculate it in the loadscript:

Sample:
LOAD * INLINE [age	contact
19	cellular
20	telephone
20	cellular
21	telephone
21	cellular
21	unknown
22	telephone
22	cellular
23	telephone
23	unknown
23	cellular
24	telephone
24	cellular
24	unknown] (delimiter is '	');

left join (Sample)
LOAD age
, if(match(concat(contact,','),'cellular,telephone,unknown'),1,0) as _Flag_All_Contact
Resident Sample
Group By age
Order by age,contact Asc
;

Now you can use _Flag_All_Contact in your expression.

Kind regards

Eddie

If this answers your question or solves your issue, be sure to mark the answer as correct by clicking 'Accept as Solution'. This will mark the post as solved and other Qlikkies will gravitate towards this post as it as a possible solution for their issue. Multiple responses can be accepted as a solution so make sure to select all that apply.
Sandip
Contributor II
Contributor II
Author

Hi @eddie_wagt  Sir,

Thank u for the reply.

But sir we are not  getting the data.

we want below type of data ...here Total balance i have create another field like Sum(balance).

age contact Total balance
21 telephone 361
21 cellular 9332
21 unknown 2727
23 telephone 1021
23 unknown 10303
23 cellular 31035
24 telephone 508
24 cellular 10502
24 unknown 4221

 

using you provided solutions we are getting below solution.

 

age contact count Total balance
19 cellular 0 1574
20 cellular 0 1693
20 telephone 0 291
21 cellular 2 9332
21 telephone 1 361
21 unknown 4 2727
22 cellular 0 12519
22 telephone 0 579
23 cellular 11 31035
23 telephone 1 1021
23 unknown 8 10303
24 cellular 11 10502
24 telephone 1 508
24 unknown 12 4221

 

How can we remove Age 19,20 who belong only one or two contact 

Regards 

Sandip 

eddie_wagt
Partner - Creator III
Partner - Creator III

Hello @Sandip ,

Ok, what do you mean by "not getting the data"? Are you trying to load the data? Or are you trying to manipulate the data in the frontend? And why is there suddenly a balance? 

You want to remove Age 19,20 but with the same rule should 22 not also being excluded?

Sorry sir, I have too many questions of what you exactly are looking for.

Regards Eddie

 

vinieme12
Champion III
Champion III

Try below in Chart

=Sum({<age={"=count({<contact={'cellular','telephone','unknown'}>}distinct contact)=3"}>}balance)

 

Or

If you need to completely remove the data during loading

temp:
LOAD * INLINE [
age,contact
19,cellular
20,telephone
20,cellular
21,telephone
21,cellular
21,unknown
22,telephone
22,cellular
23,telephone
23,unknown
23,cellular
24,telephone
24,cellular
24,unknown
];

inner join (temp)
Load age
Where countContact=3;
LOAD age
, count(distinct contact) as countContact
Resident temp
Group By age
;

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Sandip
Contributor II
Contributor II
Author

Thank you so much Sir,

it's working ...

Regards 

Sandip