Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
wu_zheng
Contributor
Contributor

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

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 ACol BCol CCol DCol E
150452015/09/07 2015/01/05 2015/09/17
251452015/07/082015/07/08 2015/07/18
351492015/07/102015/7/82015/07/18
451192015/08/08 2015/7/182015/08/08
551192015/07/12 2015/7/13/192015/7/20
651232015/10/82015/10/18 2015/11/18
751452015/11/18 2015/9/12/102015/10/18
851452015/12/20 2015/10/102015/10/22
951202015/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!




             

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

8 Replies
sunny_talwar

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

Capture.PNG

wu_zheng
Contributor
Contributor
Author

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?

wu_zheng
Contributor
Contributor
Author

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])))

wu_zheng
Contributor
Contributor
Author

Many thanks Sunny.

sunny_talwar

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:

Capture.PNG

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

Thank you