Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Partner
Partner

How to display the latest record only?

Hi All,

I have data like this:

Code            Date                  Value

1                  20130617           10

1                  20130617           20

1                  20130617           30

2                  20130617           100

2                  20130617           150

2                  20130617           200

Now in my straight tabe, I need to dislay the latest Value from above, i.e  

Code            Date                  Value

1                  20130617           30

2                  20130617           200

Please advice on how to achieve this?

Thanks

1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: How to display the latest record only?

Good idea Anders, that gives the correct result:

Screen Shot 2013-06-16 at 10.33.38 AM.png

Dmohanty: remove date dimension, add max(date) as an expression and you're good to go.

Best,

Matt

View solution in original post

8 Replies
Highlighted
MVP
MVP

Re: How to display the latest record only?

Ho do you define 'latest'? All records show the same date, is there also a timestamp you can use?

Or are you looking at the record number?

Then create a record number field in your load script:

LOAD

     Code,

     Date,

     Value,

     RecNo() as RecNr

FROM ...;

Then, in your chart table with dimension Code, use two expressions:

=FirstSortedValue(Date, -RecNr)

=FirstSortedValue(Value, -RecNr)

If your Date is actually a timestamp you can use, make sure your timestamp is a QV timestamp with a numeric representation, and replace RecNr with your Date / Timestamp field in above two expressions.

Highlighted
Not applicable

Re: How to display the latest record only?

Here's same solution Swuehl proposes using row(), but as he said, if your date includes a timestamp you should be able to use it.

Best,

Matt

Highlighted
Partner
Partner

Re: How to display the latest record only?

Hi Swuehl,

Thanks for initiating help. Unfortuantely directly I can't use the RecNo() in the load, as the fields are coming from different table. Can it be done where the fields are loaded as Resident?

I can use this expression:  FirstSortedValue(Date, -Value). It works fine.

But at the same case, I have two different dates for a single Code. In that case the above expression showing the latest value, instead of pickking the value for latest date.

Suppose, I have

Code            Date                  Value

10                  20130617           10

10                20130617           20

10                  20130617           30

10                  20130617           100

10                  20130617           150

10                 20130618           200

Now the above expression shows 150, rather than 200.

So, in a big case I have data like this:

Code            Date                  Value

10                  20130617           10

10                20130617           20

10                  20130617           30

10                  20130617           100

10                  20130617           250

10                 20130618           200

1                  20130617           10

1                  20130617           20

1                  20130617           30

2                  20130617           100

2                  20130617           150

2                  20130617           200

So the output would be

Code            Date                  Value

10                 20130618           200

1                  20130617           30

2                  20130617           200

If I use FirstSortedValue(Date, -Value), the output comes as

10                 20130618           250

1                  20130617           30

2                  20130617           250




Highlighted
Partner
Partner

Re: How to display the latest record only?

Thanks Mattsies for quick suggestion,

Suppose I did like this:

load *

,RowNo( )

Inline

[

Code ,           Date,                  Value

10 ,                 20130617,           10

10,                20130617  ,         20

10,                  20130617 ,          30

10,                  20130617 ,          100

10,                  20130617 ,          250

10,                 20130618 ,          200

1,                  20130617,           10

1 ,                 20130617,           20

1 ,                 20130617,           30

2 ,                 20130617,           100

2 ,                 20130617,           150

2 ,                 20130617,           200

];

And in the chart, I get this:

CodeDate=FirstSortedValue(Value,-[RowNo( )])
200
12013061730
220130617200
1020130617250
1020130618200

Where, I dont need the 3rd row now....here for code 10, latest value is 200.

So, I expect the data like this:

CodeDate=FirstSortedValue(Value,-[RowNo( )])
12013061730
220130617200
1020130618200

Please help on this.

Highlighted
MVP
MVP

Re: How to display the latest record only?

You are using Date as second dimension, right?

Remove that dimension and use an expression instead:

=FirstSortedValue(Date, -[RowNo()])

Highlighted
Partner
Partner

Re: How to display the latest record only?

Yes Swuehl, removing the Date as dimension and using the expression =FirstSortedValue(Value,-[RowNo( )]) gives me result like this:

Code=FirstSortedValue(Value,-[RowNo( )])
130
2200
10200

But still I need Date as the dimension.   How to proceed? Please some suggestions..

Highlighted
calvindk
Contributor III

Re: How to display the latest record only?

add the expression max(Date). that should give you the max date per Code

Highlighted
Not applicable

Re: How to display the latest record only?

Good idea Anders, that gives the correct result:

Screen Shot 2013-06-16 at 10.33.38 AM.png

Dmohanty: remove date dimension, add max(date) as an expression and you're good to go.

Best,

Matt

View solution in original post