how to pass multiple values from island table and get the output results using set analysis?

[Anonymous]

Hi ,

I have two tables, one is island table 1, it only stores calendar data, and the other one is table 2, like the following:

Table 1:

Col A

2015/07/08

2015/10/21

2015/10/18

2015/09/09

Table 2 :

Col A	Col B	Col C	Col D	Col E
1	5045	2015/09/07	2015/01/05	2015/09/17
2	5145	2015/07/08	2015/07/08	2015/07/18
3	5149	2015/07/10	2015/7/8	2015/07/18
4	5119	2015/08/08	2015/7/18	2015/08/08
5	5119	2015/07/12	2015/7/13/19	2015/7/20
6	5123	2015/10/8	2015/10/18	2015/11/18
7	5145	2015/11/18	2015/9/12/10	2015/10/18
8	5145	2015/12/20	2015/10/10	2015/10/22
9	5120	2015/11/16	2015/10/11	2015/ 10/21

I need to pass multiple selected values from table 1.[Col A], and do the comparison with Table 2.[Col C] ,  Table 2. [Col D],  and Table 2. [Col E],  and then count how many Table 2.[Col B] meets the criteria, for example: if Table 1. [Col A]>Tabel2.[Col C] or Table 1. [Col A]=Table2.[Col D] or Table 1.[Col A]<Table 2.[Col E], then count Table 2.[Col B] ,and based on the total counts on Table 2.[Col B], if the number of count is great than 2,  then the output would be the values of Table 1.[Col A].

I am new in Qlikview , and not sure if it can be done in Set Analysis.

Any help would be greatly appreciated!

[sunny_talwar]

It seems currently all the observations are meeting your one of the three criteria:

[Anonymous]

Great, Thanks Sunny for your quick response.

There are ten and thousand rows of data on Table 2, I need to find out the total number of Table1. [Col A], which is based on the total count of table 2.[Col B] with great than 2, and  the comparison between Col (from Table 1) with  Col C, Col D and Col E (from table 2)

=Sum(Aggr(If(Col > [Col C] or Col = [Col D] or Col < [Col E], Count([Col B])), Col, [Col C], [Col D], [Col E], [Col B]))

thanks again.

[sunny_talwar]

Not sure I understand? Did it work? Was the performance an issue?

[Anonymous]

The performance is no an issue, though it takes a little while to do the calculation.

but I need one more step advance, if the number of the count of Table 2. [Col B] is greater than 2,  and then the output  result would be the list of Table 1.[Col A] or the count of Table 1. [Col A].

=Count(if (=Sum(Aggr(If(Col > [Col C] or Col = [Col D] or Col < [Col E], Count([Col B])), Col, [Col C], [Col D], [Col E], [Col B]))>2, [Table 1].[Col A]))

I really appreciate your help.

[sunny_talwar]

Not able to test, but may be this:

=If(Sum(Aggr(If(Col > [Col C] or [Col A] = [Col D] or [Col A] < [Col E], Count([Col B])), Col, [Col C], [Col D], [Col E], [Col B])) > 2,

Sum(Aggr(If(Col > [Col C] or [Col A] = [Col D] or [Col A] < [Col E], Count([Col B])), Col, [Col C], [Col D], [Col E], [Col B])))

[Anonymous]

Many thanks Sunny.

[sunny_talwar · Accepted solution]

Did it get you what you were looking for? If it did, I would request you to close this thread down by marking correct and helpful answers. (Qlik Community Tip: Marking Replies as Correct or Helpful‌)

Best,

Sunny

[sunny_talwar]

Just as a suggestion, please mark the answer that was actually correct and not randomly mark any response as correct. The idea is that these posts can be seen by other users who would want to look at what was the correct response. In this case if somebody will come to this screen, he will see this as the correct response:

Is that helpful for other users? Not at all. Please make this community a better place for yourself and others

Thank you