Getting multiple values instead of single value

[former_member640919]

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 Member · Accepted solution]

What is [Jan First]?

[Former Member · Accepted solution]

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

[former_member640919 · Accepted solution]

Hi Mark,

Thanks for your reply.

Will your formula satisfy the condition, I want the max  doc satisfying the condition Valid_From _Date Between([Jan First];[Report_Date]) and get the rec_id for the max doc

I got this doubt because you are getting max(doc) in (Eqp_Id) without satisfying the condition Valid_From _Date Between([Jan First];[Report_Date]) first.  and  also getting max(Rec_Id).

Can you explain your formula  in brief?

[former_member210032]

Hi Jyothy,

Your expected out like that if yes then apply max function for both objects

           Rec_id     Doc_No

                R3            376

Regards

Mustafa

[former_member640919]

Hi,

I can't apply max Rec_Id because I have to get Rec_id for Max doc_No and in my case Rec_id is a string i.e. Name

[kohesco]

Hi,

Check out document :

grtz

Koen

[former_member210032]

Hi Jyothy,

Create a variable

Rec_Id1 =Max([Rec_Id])

see the below screenshot....

Regards

Mustafa

[former_member640919]

Hi,

I have to get Rec_id for max(Doc_No).

Your solution will not work. As it will get me only max Rec_Id and Rec_Id is a string.