Yes/No/- table aggregation

[#1]

I have the following tables:
                Table 1:                                         Table 2:

Person	Contacted	Person	Contacted Ever
1	Yes	1	Yes
1	No	2	No
1	No	3	Yes
2	No	4	Yes
2	-	5	Yes
2	-	6	Yes
3	Yes	7	Yes
3	Yes	8	No
3	No
4	Yes
4	Yes
4	Yes
5	No
5	Yes
5	-
6	-
6	Yes
6	-
7	No
7	Yes
7	No
8	-
8	No
8	No

I would like to know how I can aggregate it in such a way, that it returns Yes, whenever there is even a single 'Yes' for the specific person in Table1.

I want it to return 'No', only when there is no single Yes given

[alexandros17]

TABLE1:
load * Inline [
Person, Contacted
1, Yes
1, No
1, No
2, No
2, -
2, -
3, Yes
3, Yes
3, No 
4, Yes 
4, Yes 
4, Yes 
5, No 
5, Yes 
5, - 
6, - 
6, Yes 
6, - 
7, No 
7, Yes 
7, No 
8, - 
8, No 
8, No
];

Left Join
LOAD Person, Sum(If(Contacted='Yes',1,0)) as numYes Resident TABLE1 Group By Person;

In the field numYes you see (if > 0) if there is at least a yes

[maxgro]

TABLE1:

load Person, if(Contacted='-', null(), Contacted) as Contacted Inline [

Person, Contacted

1, Yes

1, No

1, No

2, No

2, -

2, -

3, Yes

3, Yes

3, No 

4, Yes 

4, Yes 

4, Yes 

5, No 

5, Yes 

5, - 

6, - 

6, Yes 

6, - 

7, No 

7, Yes 

7, No 

8, - 

8, No 

8, No

9, -

];

TABLE2:

load

Person as NewPerson, maxstring(if(IsNull(Contacted),'No', 'Yes')) as NewContacted

Resident TABLE1

group by Person;

[MarcoWedel]

Hi,

one solution in the front end:

hope this helps

regards

Marco

[ToniKautto]

In load script I would suggest enumerating the Yes and No. Then you aggregated the Yes or No by grouping over Person, and join the result back to the main table. In your minimized example there is no obvious need to have separate tables.

[MayilVahanan]

Hi

Try like this

LOAD Person, If(Contacted >=1, 'Yes', 'No') as Contacted;

LOAD Person, sum(if(Contacted = 'Yes',1, 0)) as Contacted group by Person;

load * Inline [

Person, Contacted

1, Yes

1, No

1, No

2, No

2, -

2, -

3, Yes

3, Yes

3, No 

4, Yes 

4, Yes 

4, Yes 

5, No 

5, Yes 

5, - 

6, - 

6, Yes 

6, - 

7, No 

7, Yes 

7, No 

8, - 

8, No 

8, No

];

[#7]

Thank you guys a lot for your help and cooperation.

For my specific case I was able to make best use of and modify Marco Wedel's advice. 

Cheers!

[#8]

Hey Guys, a new problem appeared.

As I said earlier I used Marco's solution.

If I apply it to my data model, there I have dates and I have a person that was not contacted in period 1, but was in period 2, I will get contacted ever - yes. However, if I select only period 1 in the time frame it will return to me contacted ever - no, which is not true as he was contacted in period 2. How can I fix this, so the contacted ever value is not dependant on the time frame selection?

[swuehl]

Try

=Maxstring({1} Contacted)

for a selection insensitive result

or

=Maxstring({<YourDateField=, YourPeriodField= >} Contacted)

to clear only selections in your calendar field (you need to add all your calendar fields you may select in)