How can get the data

[Sandip]

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

[eddie_wagt]

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]

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]

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 · Accepted solution]

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
;

[Sandip]

Thank you so much Sir,

it's working ...

Regards 

Sandip