Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

sprqlikview
New Contributor II

Firstsortedvalue issue

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

    

ArticlePrognoseIDFlagAFlagB
14417128ca
1441792ca
1441781ca
14470135oa
14470119oa
1447087oa

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Firstsortedvalue issue

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?

10 Replies
antoniotiman
Honored Contributor III

Re: Firstsortedvalue issue

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
New Contributor III

Re: Firstsortedvalue issue

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;

MVP
MVP

Re: Firstsortedvalue issue

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
New Contributor II

Re: Firstsortedvalue issue

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

sprqlikview
New Contributor II

Re: Firstsortedvalue issue

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

MVP
MVP

Re: Firstsortedvalue issue

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
New Contributor II

Re: Firstsortedvalue issue

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

MVP
MVP

Re: Firstsortedvalue issue

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
New Contributor II

Re: Firstsortedvalue issue

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

Clipboard01.jpg

Thanks for your help.