Using a table alias to identify row with max timestamp for same ID, syntax?

[former_member283700]

Hello experts

I have created an alias of a table, so now I have table T1 and its alias T2. (T2 is not joined to anything in the universe currently)

I need to identify the row from T1 with the maximum timestamp for any given ID:

ID Timestamp

1 2011-01-24 16:26:00.000

1 2011-02-24 14:21:00.000

1 2011-02-24 13:49:00.000

I couldn't find anything on the SAP forums, but elsewhere suggested my approach should be thus:

1) Create a table alias (leave it free standing i.e. not joined) - DONE

2) For T1, create a dimension object named Timestamp - DONE

3) Create a seperate predefined condition icon funnel / filter - in the where clause:

T1.timestamp = (SELECT max(T2.timestamp) from T2 WHERE

T1.Key = T2.Key)

I'm stuck with the BO XI 3.1 syntax on step 3. I can't get it to parse.

In the where clause, mine starts with @select(T1\Timestamp) = max(@select(T2\Timestamp)

@where T1.Claim_no = T2.Claim_no)

Please can someone help me with the syntax so this thing will parse.

Many thanks in anticipation.

Eddie

[Former Member]

Hi ,

Can you try

SELECT ID, MAX(datetime) FROM T1 GROUP BY by ID

Thanks

Ponnarasu

[former_member283700]

Thanks but I couldn't get that to work. No worries, I'll try some other forums again.

Eddie