topic Re: How to display the latest record only? in QlikView

How to display the latest record only?

dmohanty — Sat, 15 Jun 2013 13:48:21 GMT

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

Re: How to display the latest record only?

swuehl — Sat, 15 Jun 2013 14:06:46 GMT

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.

Re: How to display the latest record only?

Sat, 15 Jun 2013 14:20:36 GMT

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

Re: How to display the latest record only?

dmohanty — Sat, 15 Jun 2013 14:31:45 GMT

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

Re: How to display the latest record only?

dmohanty — Sat, 15 Jun 2013 14:43:51 GMT

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:

Code	Date	=FirstSortedValue(Value,-[RowNo( )])
		200
1	20130617	30
2	20130617	200
10	20130617	250
10	20130618	200

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

So, I expect the data like this:

Code	Date	=FirstSortedValue(Value,-[RowNo( )])
1	20130617	30
2	20130617	200
10	20130618	200

Please help on this.

Re: How to display the latest record only?

swuehl — Sat, 15 Jun 2013 14:53:25 GMT

You are using Date as second dimension, right?

Remove that dimension and use an expression instead:

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

Re: How to display the latest record only?

dmohanty — Sat, 15 Jun 2013 15:00:09 GMT

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

Code	=FirstSortedValue(Value,-[RowNo( )])
1	30
2	200
10	200

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

Re: How to display the latest record only?

calvindk — Sat, 15 Jun 2013 16:51:59 GMT

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

Re: How to display the latest record only?

Sun, 16 Jun 2013 14:36:28 GMT

Good idea Anders, that gives the correct result:

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

Best,

Matt