Search
Search

Getting multiple values instead of single value

Hi,

We have Eqp_Id, Doc_No, Valid_From_Date, Report_Date Rec_Id

66      199     6/18/2014        9/30/2014  R1

66      200     6/18/2014        9/30/2014  R2

66      338     7/28/2014        9/30/2014  R2

66      375     8/20/2014        9/30/2014  R2

66      376     8/20/2014        9/30/2014  R3

I want output Rec_Id for Maximum Doc_No

I am using two formulaes:

Max_Doc_No: =Max([Doc_N]) Where ([Valid_From_Date] Between([Jan First];[Report_Date]) )

Rec=[Rec_Id] Where ([Doc_No]=[Max_Doc_No])

When I output Eqp_id, Max_Doc_No, I am getting

66      376

When I output, Eqp_Id, Rec Max_Doc_No

66     R1   199

66      R2   375

66     R3   376

I want output Eqp_Id Rec_Id

66   R3

If I am using formula: [Rec_Id] Where ([Doc_No]=Max([Doc_N]) Where ([Valid_From_Date] Between([Jan First];[Report_Date]) )), I am getting multi value error

Former Memberreplied

If it's just a variable holding 1st Jan of current year then create a variable MaxRecInEqp as:

=Max([Rec_Id]) Where ([Doc_No]=Max([Doc_No]) In ([Eqp_Id]) And [Valid_From_Date] Between([Jan First];Max([Report_Date]) In ([Eqp_Id])))

Now create a table with Eqp_Id and MaxRecInEqp as the two columns.

Regards,

Mark

0 View this answer in context
View more on this topic or