topic Re: FirstSorted Value? in QlikView

FirstSorted Value?

Anonymous — Wed, 04 Mar 2015 14:03:07 GMT

Hello all, I have a table, see below.

ID Affiliation School District

1 Red Western 25

1 Blue Western 25

2 Green Eastern 52

3 Yellow Main 11

I would like to pick the first value in Affiliation, so the table would read...

ID Affiliation School District

1 Red Western 25

2 Green Eastern 52

3 Yellow Main 11

I would like to able to do add this in the script and in the list box in the future. Can someone give me the correct way to add with the smaple I used above? Assuming it would go something like below...Thank you for the help, much appreciated!

Fistsortedvalue([Affiliation] ....

Re: FirstSorted Value?

rwunderlich — Wed, 04 Mar 2015 16:31:52 GMT

In script it would be:

LOAD ID,

FirstSortedValue(Affiliation,recno()) as Affiliation,

School,

District

INLINE [

ID, Affiliation, School, District

1, Red, Western, 25

1, Blue, Western, 25

2, Green, Eastern, 52

3, Yellow, Main, 11

]

Group BY ID, School, District;

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: FirstSorted Value?

Anonymous — Wed, 04 Mar 2015 16:44:37 GMT

Blah, I could do that but now that I think about it, it would be cleaner from the table box. Would you happen to know that process? An example from my data would help...

Re: FirstSorted Value?

rwunderlich — Wed, 04 Mar 2015 16:59:26 GMT

I think you'll need to add a load sequence field in the load script to be able to identify what is the first one. In your script, add

RecNo() as RecId

Then in a chart:

FirstSortedValue(Affiliation,RecId)

Re: FirstSorted Value?

Anonymous — Wed, 04 Mar 2015 19:32:41 GMT

Anyone else want to add their feedback. Again I would like to take the first of a value in a filed from a table box, see example from question...

Re: FirstSorted Value?

rwunderlich — Wed, 04 Mar 2015 23:27:36 GMT

I looked at this again. I think you can do it in an expression as:

=subfield(Concat(Affiliation,'|'),'|',1)

-Rob