Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Lookup value using expressions

In the following straight table I would like the third column "EDC_LMT_HIGH to obtain the value for the latest "Begin_Date" that falls on or before a given effective date. Is this possible via an expression?

table.PNG

Here are the other tables.

limits.PNG

effective.PNG

1 Solution

Accepted Solutions
MVP
MVP

Lookup value using expressions

Jason,

you could probably resolve your request using FirstSortedValue() and a set expression to limit the BEGIN_DATE values you want to look up.

Something along

=FirstSortedValue({<BEGIN_DATE = {"<=$(=max(EFFECTIVE_DATE))"} >} EDC_LMT_HIGH, -BEGIN_DATE)

Please check out FirstSortedValue and set analysis in the Help file.

This should work in a table with dimension NAME, above expression and if you want the BEGIN_DATE accordingly:

= max({<BEGIN_DATE = {"<=$(=max(EFFECTIVE_DATE))"} >} BEGIN_DATE)

Or maybe I misunderstood your setting, so some sample rows of your data, best coded as INLINE table or in a small sample app might help.

Regards,

Stefan

2 Replies
MVP
MVP

Lookup value using expressions

Jason,

you could probably resolve your request using FirstSortedValue() and a set expression to limit the BEGIN_DATE values you want to look up.

Something along

=FirstSortedValue({<BEGIN_DATE = {"<=$(=max(EFFECTIVE_DATE))"} >} EDC_LMT_HIGH, -BEGIN_DATE)

Please check out FirstSortedValue and set analysis in the Help file.

This should work in a table with dimension NAME, above expression and if you want the BEGIN_DATE accordingly:

= max({<BEGIN_DATE = {"<=$(=max(EFFECTIVE_DATE))"} >} BEGIN_DATE)

Or maybe I misunderstood your setting, so some sample rows of your data, best coded as INLINE table or in a small sample app might help.

Regards,

Stefan

Not applicable

Lookup value using expressions

Thank You.

Community Browser