simple firstsortedvalue

[shahamel]

Dear QV-users,

I have a question

I have

LOAD * INLINE [
    JAAR, NIVEAU
    2000, NIVEAU1
    2000, NIVEAU2
    2001, NIVEAU4
    2002, NIVEAU2
    2002, NIVEAU3
    2003, NIVEAU1
    2003, NIVEAU2
    2003, NIVEAU3
    2003, NIVEAU3
];

I would like to create a table as in Niveautest.xslx (see attachmant).

[shahamel]

according to me it should be something like ths:

=

firstsortedvalue(JAAR,rangemax(aggr(count(distinct NIVEAU),JAAR)) )

[swuehl]

Try creating a chart with dimension JAAR and as expression either

=maxstring(NIVEAU)

or maybe something like

=FirstSortedValue(DISTINCT NIVEAU, -keepchar(NIVEAU,'0123456789'))

[You can preprare the numeric sort weight of course also in the script, generating an additional field]

[jeffmartins]

Hi shahamel,

You can solve this using the LastValue function and grouping the values by the JAAR field.

See the attached file. Hope this helps you.

Regards

[shahamel]

This is a correct answer, but i would like to have a similar result, by just using an expression. I would like to do nothing within the script.

This problem has to do with this problem

http://community.qlik.com/message/304529#304529

In the discussion on this page i created a simplyfied situation. But the result i am looking for is stated in the txt.fle in the discussion on this url:

http://community.qlik.com/message/304529#304529

Gr. Sandeep

[jeffmartins · Accepted solution]

Hi shahamel,

I've created an expression to solve this problem, the expression is a little complex so, I created a solution 2 that is simpler and faster, but it uses a flag created in script.

Solution 2

1) In Script - Mark with a flag the records you want show.

2) In Layout - Use the flag with set analysis in the expression.

See the attached file. Hope it helps you.

Regards

[shahamel]

Thank you so much .

SubField

( Concat(distinct NIVEAU,',',JAAR),
',',

   SubStringCount(concat(DISTINCT NIVEAU,',',JAAR),',')+1

)

that works, but i don't understand yet what is happening here.

when you have spare time, could you explain above's expression?

Gr. Sandeep

[shahamel]

I have another question on top of that. It is possible to nest your expression within another expression?