topic Firstsortedvalue issue in QlikView

Firstsortedvalue issue

sprqlikview — Thu, 02 Nov 2017 10:52:46 GMT

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

Re: Firstsortedvalue issue

antoniotiman — Thu, 02 Nov 2017 11:02:00 GMT

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;

Re: Firstsortedvalue issue

srivastalans — Thu, 02 Nov 2017 11:14:41 GMT

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;

Re: Firstsortedvalue issue

sunny_talwar — Thu, 02 Nov 2017 11:18:25 GMT

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;

Re: Firstsortedvalue issue

sprqlikview — Thu, 02 Nov 2017 12:08:40 GMT

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

Re: Firstsortedvalue issue

sprqlikview — Thu, 02 Nov 2017 12:09:57 GMT

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

Re: Firstsortedvalue issue

sunny_talwar — Thu, 02 Nov 2017 12:11:22 GMT

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;

Re: Firstsortedvalue issue

sprqlikview — Thu, 02 Nov 2017 12:16:35 GMT

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

Re: Firstsortedvalue issue

sunny_talwar — Thu, 02 Nov 2017 12:22:51 GMT

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?

Re: Firstsortedvalue issue

sprqlikview — Thu, 02 Nov 2017 12:29:28 GMT

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.

Re: Firstsortedvalue issue

sunny_talwar — Thu, 02 Nov 2017 12:30:46 GMT

May be you are using an older version of QlikView because I don't see the red wiggly lines. I think they updated the syntax editor with QV12 release