Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

FirstSorted Value?

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] ....

5 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Anonymous
Not applicable
Author

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...

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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)

Anonymous
Not applicable
Author

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...

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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

-Rob