Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

shearwatertroy
New Contributor III

First sorted value or max?

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.