Help me to get records based on indicator?

[durgabhavani]

Hi,

Can you please help me to get records based on indicator.

We need to display the record if the indicator having value 1 if mulple records. if there is only one record with one indicator then we need to display the record whatever it is.

Please find the sample data and expected output below.

ID	Name	Indicator
101	Hari	1
102	Sunil	0
103	Satish	1
103	Satish	0
104	Ramesh	1
104	Ramesh	0
105	Naresh	1
106	Venkat	1
106	Venkat	0
106	Venkat	0
107	Ravi	0
108	Raju	0
108	Raju	0

Expected Output:

ID	Name	Indicator
101	Hari	1
102	Sunil	0
103	Satish	1
104	Ramesh	1
105	Naresh	1
106	Venkat	1
107	Ravi	0
108	Raju	0

Please advice.

Thanks in advance.

Durga

[neelamsaroha157 · Accepted solution]

Check this out.

[tresesco]

A front end solution:

Exp: =If( Aggr(Count(if(Indicator=1, Indicator)),ID)>0,Only({<Indicator={1}>}Indicator), Indicator)

[Kushal_Chawda]

May be

Data:

LOAD ID,

           Name,

           sum(distinct Indicator) as Indicator

FROM Source

Group by

          ID,

          Name;

[sunny_talwar]

for a chart based solution

[durgabhavani]

Thank you All. Very useful solutions.

if there is null value in indicator. how to handle it?

is max(indicator) will work?.

[neelamsaroha157]

If there is only 1 records per ID with null Indicator then it would show null.

If there are multiple records fro 1 ID and one of them is null then it will pick up the max value.

If you want to treat null records separately then you can handle it with extra if condition like If(IsNull(Indicator), Condition, else condition).

Hope this helps.