Firstsortedvalue issue

[sprqlikview]

I've read a lot about the function firstsortedvalue() but do not get the expected result.

I have a table with 4 fields.

- PrognoseID (1-150)

- Article (numbers)

- Flag A: letters

- Flag B: letters

Example

Article	PrognoseID	FlagA	FlagB
14417	128	c	a
14417	92	c	a
14417	81	c	a
14470	135	o	a
14470	119	o	a
14470	87	o	a

Some Articles are in different IDs with different Flags.

My goal is to have Flag A and B for each Article based on the highest ID number. For Article 14417 this would be c & a from PrognoseID 128.

For some reason, my ressult is blanc entries for Flag A & B for most of the articles. I hope one of you can help me.

Below the script;

Sortiment_Temp:

LOAD PrognoseID,

     Article,

     FlagA,

     FlagB

FROM

[..\03_Datasource\Sortiment.qvd]

(qvd) ;

NoConcatenate

Sortiment:

Load

max(PrognoseID) as PrognoseID,

     Article,

     FirstSortedValue(FlagA,PrognoseID) as FlagA

     FirstSortedValue(FlagB,PrognoseID) as FlagA

Resident Sortiment_Temp

Group by Article

[antoniotiman]

May be this

Table:
LOAD * Inline [
Article PrognoseID FlagA FlagB
14417 128 c a
14417 92 c a
14417 81 c a
14470 135 o a
14470 119 o a
14470 87 o a
] (delimiter is spaces);
Inner Join
LOAD Max(PrognoseID) as PrognoseID
Resident Table
Group By Article;

[srivastalans]

Try this,

LOAD Article,Max(PrognoseID) as PrognoseID, FlagA, FlagB INLINE [

    Article, PrognoseID, FlagA, FlagB

    14417, 128, c, a

    14417, 92, c, a

    14417, 81, c, a

    14470, 135, o, a

    14470, 119, o, a

    14470, 87, o, a

]

Group by Article, FlagA, FlagB;

[sunny_talwar]

May be try this

Sortiment:

Load max(PrognoseID) as PrognoseID,

    Article,

    FirstSortedValue(DISTINCT FlagA, -PrognoseID) as FlagA

    FirstSortedValue(DISTINCT FlagB, -PrognoseID) as FlagB

Resident Sortiment_Temp

Group by Article;

[sprqlikview]

I get an error if I add Distinct. The minus before PrognoseID I tried before but it does not make a difference.

[sprqlikview]

Thanks for the suggestion but I still get an multiple entries per article.

[sunny_talwar]

Seems like the FlagA and FlagB don't really change? If that is true then make them part of your Group By?

Sortiment:

Load max(PrognoseID) as PrognoseID,

    Article,

    FlagA

    FlagB

Resident Sortiment_Temp

Group by Article, FlagA, FlagB;

[sprqlikview]

It's possible that they change and therefore I want the latest one.

In the example I should, for example, have used:

14417, 128, c, a

    14417, 92, b, d

    14417, 81, c, a

    14470, 135, o, a

    14470, 119, f, d

    14470, 87, o, a

[sunny_talwar · Accepted solution]

I tried this... and had no errors

Table:

LOAD * INLINE [

    Article, PrognoseID, FlagA, FlagB

    14417, 128, c, a

    14417, 92, b, d

    14417, 81, c, a

    14470, 135, o, a

    14470, 119, f, d

    14470, 87, o, a

];

FinalTable:

NoConcatenate

LOAD Article,

Max(PrognoseID) as PrognoseID,

FirstSortedValue(DISTINCT FlagA, -PrognoseID) as FlagA,

FirstSortedValue(DISTINCT FlagB, -PrognoseID) as FlagB

Resident Table

Group By Article;

DROP Table Table;

What errors were you getting when you used DISTINCT?

[sprqlikview]

It worked. I think I was confused because the script was red underlined but when I run the script it works. Maybe a bug.

Thanks for your help.