0 Replies Latest reply: Feb 28, 2017 10:47 AM by Troy Strachan RSS

    First sorted value or max?

    Troy Strachan

      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.