First sorted value or max?

shearwatertroy — Tue, 28 Feb 2017 15:47:35 GMT

Hey everyone I'm having some issues with some duplicate data.

We have sales basic sales data:

And what I'm finding is that when [Version No_] is greater than 1 I'm getting multiple (non distinct) [Document No_]. I've tried using first sorted value as well as max functions.

I've tried following the examples in a couple other problems but they just don't seem to jive with my table.

functions I've tried:

firstsortedvalue([Document No_], [Version No_]) - to see if I would get distinct Document No_

firstsortedvalue([Document No_], -[Version No_]) - to get only the highest number

firstsortedvalue(distinct([Document No_]), [Version No_]) - to see if that would give me distinct Document No_

firstsortedvalue([Version No_], [Document No_]) - same process as above

firstsortedvalue([Version No_], -[Document No_])

firstsortedvalue(distinct([Version No_]), [Document No_])

I've also tried using a SQL statement on the script to only grab the max [Version No_] which also has not worked.(This SQL statement gives me the data I need):

Select * from "Sales Line Archive" SLA1

WHERE SLA1.[Version No_] =

(Select Max(SLA2.[Version No_]) from "Sales Line Archive" SLA2 Where SLA2.[Document No_] = SLA1.[Document No_])

AND ("No_" = '11001' OR "No_" = '11004' OR "No_" = '12001' OR "No_" = '13001' OR "No_" = '13003');

Any help is greatly appreciated.

topic First sorted value or max? in App Development

First sorted value or max?