topic Re: simple firstsortedvalue in QlikView

simple firstsortedvalue

shahamel — Thu, 31 Jan 2013 12:26:28 GMT

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

Re: simple firstsortedvalue

shahamel — Thu, 31 Jan 2013 12:40:52 GMT

according to me it should be something like ths:

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

Re: simple firstsortedvalue

swuehl — Thu, 31 Jan 2013 12:42:20 GMT

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]

Re: simple firstsortedvalue

jeffmartins — Thu, 31 Jan 2013 12:45:37 GMT

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

Re: simple firstsortedvalue

shahamel — Thu, 31 Jan 2013 12:51:22 GMT

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

Re: simple firstsortedvalue

jeffmartins — Thu, 31 Jan 2013 17:30:50 GMT

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

Re: simple firstsortedvalue

shahamel — Mon, 04 Feb 2013 11:54:59 GMT

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

Re: simple firstsortedvalue

shahamel — Mon, 04 Feb 2013 11:55:48 GMT

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