topic Re: How to identify values according to a restriction in QlikView

How to identify values according to a restriction

Anonymous — Thu, 24 Mar 2016 18:01:53 GMT

Dear community:

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

I have the following tables:

Table1

Table2

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 — Thu, 24 Mar 2016 18:13:20 GMT

This?

Script:

Table:

LOAD Start,

End,

AmountYears,

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 — Thu, 24 Mar 2016 18:18:55 GMT

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

sunny_talwar — Thu, 24 Mar 2016 18:24:14 GMT

Not good for performance, but this should work:

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

swuehl — Thu, 24 Mar 2016 18:25:34 GMT

Or maybe this with the tables shown above:

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