How to display the latest record only?

[dmohanty]

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

[swuehl]

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.

[#3]

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

[dmohanty]

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

[dmohanty]

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.

[swuehl]

You are using Date as second dimension, right?

Remove that dimension and use an expression instead:

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

[dmohanty]

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

[calvindk]

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

[#9 · Accepted solution]

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