topic Re: Yes/No/- table aggregation in QlikView

Yes/No/- table aggregation

Wed, 03 Jun 2015 15:08:48 GMT

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

Re: Yes/No/- table aggregation

alexandros17 — Wed, 03 Jun 2015 15:14:43 GMT

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

Re: Yes/No/- table aggregation

maxgro — Wed, 03 Jun 2015 22:20:14 GMT

TABLE1:

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

Person, Contacted

1, Yes

1, No

2, No

2, -

3, Yes

3, No

4, Yes

5, No

5, Yes

5, -

6, -

6, Yes

6, -

7, No

7, Yes

7, No

8, -

8, No

9, -

];

TABLE2:

load

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

Resident TABLE1

group by Person;

Re: Yes/No/- table aggregation

MarcoWedel — Wed, 03 Jun 2015 23:58:34 GMT

Hi,

one solution in the front end:

hope this helps

regards

Marco

Re: Yes/No/- table aggregation

ToniKautto — Thu, 04 Jun 2015 02:28:47 GMT

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.

Re: Yes/No/- table aggregation

MayilVahanan — Thu, 04 Jun 2015 05:37:42 GMT

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

2, No

2, -

3, Yes

3, No

4, Yes

5, No

5, Yes

5, -

6, -

6, Yes

6, -

7, No

7, Yes

7, No

8, -

8, No

];

Re: Yes/No/- table aggregation

Thu, 04 Jun 2015 13:35:20 GMT

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!

Re: Yes/No/- table aggregation

Fri, 05 Jun 2015 13:34:45 GMT

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?

Re: Yes/No/- table aggregation

swuehl — Fri, 05 Jun 2015 20:19:01 GMT

Try

=Maxstring({1} Contacted)

for a selection insensitive result

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

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