Avoid repetitions

AxelB · 2017-09-12T10:58:50+00:00

Hi, assuming I have a data set table like this: ID Timestamp State 1 2017-06-03 New 1 2017-06-04 Started 1 2017-06-08 Ongoing 1 2017-06-24 Solved 2 2017-05-01 New 2 2017-07-25 Started 2 2017-07-31 Ongoing 3 2017-04-17 New 3 2017-04-25…

AxelB

Hi,
assuming I have a data set table like this:
ID Timestamp State
1 2017-06-03 New
1 2017-06-04 Started
1 2017-06-08 Ongoing
1 2017-06-24 Solved
2 2017-05-01 New
2 2017-07-25 Started
2 2017-07-31 Ongoing
3 2017-04-17 New
3 2017-04-25 Started
4 2017-06-01 New
4 2017-06-01 Started
4 2017-06-05 Ongoing
4 2017-07-14 Solved
 
But I only want to see the latest entry (ID & State) before a certain key-date.
Assuming, keydate is 2017-06-30
I would like to see the following result in the data set preview:
ID Timestamp State
1 2017-06-24 Solved
2 2017-05-01 New
3 2017-04-25 Started
4 2017-06-05 Ongoing
 
Is there a smart way to get this implemented?
The Data Set is bound to a Dynamic Text Field. I only see to run through the complete list in 'On Fetch' and just store the latest entry for an 'ID'. But maybe there is a more smarter way to get this solved already on data set level.
 
Thx
Axel

Find more posts tagged with

Comments

shamo

What I will do will in the query will be to use max(timestamp) over ( partition by Id order by id asc) as newname
 
then will use a filter like timestamp = newname. this will give you the results.
 
Hope this helps

AxelB

Hi Shamo,
thanks for the input, I understand what you're intention is.
But I'm failing on the implementation part.
 
I'm working with BIRT in Eclipse. I've now added a ComputedColumn 'Newname' with Expression 'row["TIMESTAMP"]' and Aggregation ='MAX'.
But this leads to that every entry gets the same timestamp.
 
I cannot simply transfer this SQL expression "max(row["TIMESTAMP"]) over ( partition by row["ID"] order by row["ID"] asc)" to the Expression Builder?
 
Thx

wwilliams

I believe Shamo was suggesting that you use that in the open of you data set e.g.
maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName()); 
maximoDataSet.open(); 
var sqlText = new String(); 
// Add query to sqlText variable. 
sqlText = "SELECT max(timestamp) over....