How to identify values according to a restriction

[Anonymous]

Dear community:

I'm having a problem that I hope you can help me with.

I have the following tables:

  Table1

Start	End	AmountYears	ID
2007	2012	5	2
2011	2016	5	3
2012	2018	6	5
2012	2018	6	7
2012	2016	4	8
2009	2014	5	10
2015	2019	4	11

Table2

year
2010
2011
2012
2013
2014
2015
2016

I need to show the amount of ID's that in the year selected in the table 2, the value in the End table is equal or higher than it.

For example, if the year selected in the table2 is 2015, the amount of ID's are 5: ID's 3,5,7,8,1.

Thanks alot

[sunny_talwar]

This?

Script:

Table:

LOAD Start,

     End,

     AmountYears,

     ID

FROM

[https://community.qlik.com/thread/210882]

(html, codepage is 1252, embedded labels, table is @1);

Table2:

LOAD year

FROM

[https://community.qlik.com/thread/210882]

(html, codepage is 1252, embedded labels, table is @2);

Left Join(Table)

IntervalMatch(year)

LOAD Start,

  End

Resident Table;

Left Join (Table)

LOAD year

Resident Table2;

DROP Table Table2;

[Anonymous]

is there a way to do it on the front end? like an expression on a text object?

[sunny_talwar]

Not good for performance, but this should work:

=Count(Aggr(If(year >= Start and year <= End, ID), year, ID))

[swuehl]

Or maybe this with the tables shown above:

=CONCAT(DISTINCT If(year>=Start and year<=End, ID),', ',ID)