Row Selection

[#1]

Hi Everyone,

I have following Requirement,

Table1:

EntryNo	ItemNo	PostingDate
E01	I001	05/07/2012
E02	I001	06/07/2012
E03	I002	08/07/2012
E04	I003	01/07/2012
E05	I001	30/06/2012
E06	I002	25/06/2012

Table2:

BusGroup	ProductGroup	ItemNo	FromDate
EBPG-001	RM	I001	02/07/2012
EBPG-001	RM	I001	03/07/2012
EBPG-001	RM	I002	05/07/2012
EBPG-001	PM	I002	08/07/2012
EBPG-001	PM	I003	06/07/2012
EBPG-001	RM	I001	09/07/2012

EntryNo-PostingDate(one to one)

When I will select BusGroup EBPG-001,ProductGroup RM & ItemNo I001, Then I will get multiple FromDate (02/07/2012,03/07/2012,9/07/2012)

When I will select EntryNo  E02 for the same ItemNo I001 then I will get PostingDate 06/07/2012

Now,I want to select that row from Table2 whose FromDate is Maximum Among all dates that are less than or equal to PostingDate for EBPG-001 & RM Combination

So in this case,It will select FromDate 03/07/2012 which is less than 06/07/2012.

How can I do this??

Thanks.

[kaushiknsolanki · Accepted solution]

Hi,

   Have a look at the application attched here with.

   All you have to do is Create a chart with Two dimensions those are EntryNo and ItemNo and include following expression.

   max({<FromDate = {"<$(=max(PostingDate))"} >}FromDate)

   This will give you the max FromDate where the FromDate is lesser then Posting date.

    Hope this will solve your problem.

Regards,

Kaushik Solanki

[#3]

Hi Kaushik,

Thanks for correct reply. The expression is working as per the requirement.