topic Re: Use only latest date in QlikView

Use only latest date

Fri, 07 Feb 2014 17:44:24 GMT

Hi everybody!

This is my first post, I´m new at QlikView.

I´ve two tables. This could be a list of pupils, with their points at the last tests in class (see Table1). With every new Test, the teacher adds a new row with the current date and the achieved score. The teacher wants to have a straight table in QlikView, where he could see only the last scores of every pupil.

Therefore I used:

Dimension: Name

Expression: if(Testdate = max(Testdate), History)

Than I get a field with the score of the last History-test of every pupil.

But there was a problem. At "Scott" it shows me only "-" because he missed the last test.

For that, I would like to have an expression, that would use Max(Testdate) and if there is no score at this date, it would use the latest available score. At Scott it would be the 20.12.13 (see Table2). So in future, when the teacher will have more tests with this class, he will always have the latest score of every pupil, even when he misses a test.

I already looked at the community and at google. I also read the book "QlikView 11 for Developers", but I didn´t find a solution yet. Maybe I found it, but didn´t recognise it.

Thanks a lot for your help!

Re: Use only latest date

swuehl — Fri, 07 Feb 2014 17:53:13 GMT

Look into FirstSortedValue() function, create a chart with dimension Name, then

=max(Testdate)

as 1. expresseion to get the latest Testdate per Name, and three other expressions:

=FirstSortedValue( History, -Testdate)

=FirstSortedValue( Math, -Testdate)

=FirstSortedValue( Physics, -Testdate)

Re: Use only latest date

Fri, 07 Feb 2014 18:40:10 GMT

Hi swuehl,

that was pretty fast. Thank you very much. It works fine, if there is no row with a date and "-". But I the case of "Scott", he hast a date and three "-".

Ok, in this case I could drop the whole row. But for example, it could be, that Scott made 2 Tests (History and Physics) and missed one (Math). Than it would look like this:

In this case, I would like to have at Scott the current tests in History and Physics, and for Math the score of his last Math test, at the 20.12.13 (= 73). Please don´t think about the reason, the teacher want´s to have always a value to have not only a "-". 🙂

Thanks a lot!

Re: Use only latest date

swuehl — Fri, 07 Feb 2014 18:47:36 GMT

Yes, I missed the line with no scores for Scott.

It would be easier if you could upload some sample lines of data (easier to use the data compared to a screenshot).

In general, you should be able to include a condition or a so called set expression to filter only valid scores.

Re: Use only latest date

Fri, 07 Feb 2014 19:55:23 GMT

Best way to handle this issue is... to write a load script to replace null with score of previous test

Re: Use only latest date

Fri, 07 Feb 2014 21:14:34 GMT

Hi Swehl,

can you please post condition for that.

regards

Mahesh T

Re: Use only latest date

Sat, 08 Feb 2014 11:30:42 GMT

Thank you swuehl for your quick answer and your help. So I will go deeper into set expressions.

@Surya: Thank you for your tip. I will try this one, too.

When I get a solution of that, I will post it.