Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

How to get latest transaction date?

Hi guys,


Just wanted to ask how to get the LATEST transaction date for every customer using INFORMATION DESIGN TOOL?

For example, this is my raw data:

Transaction DateCustomerMaterialAmount
20160105AX10
20160121AX20
20160210AX30
20160225AX40
20160304AX50
20160316AX60
20160103BX70
20160108BX80
20160124BX90
20160206BX100
20160215BX110
20160218BX120

I wanted to have this query:

My parameter is transaction date..So if user entered 20160201 - 20160229, this will be my result..


CustomerLast Transaction DateAmount
A2016022540
B20160218120

So if user again entered 20160101 - 20160131, this will be my result..


CustomerLast Transaction DateAmount
A2016012120
B2016012490

I tried to use the MAX function but did not get the desired result..Is there a CONDITION in universe similar to query designer?



Thank you.

Loed

Tags:
replied

Hi,

You need to create a derived table in the data foundation like this:

SELECT

  A.Customer,

  Max(A.Last_Transaction_Date) as Last_Transaction_Date,

  Sum(Amount) as Amount

FROM My_Table B,

  (SELECT Customer, Last_Transaction_Date

  FROM My_Table

  WHERE

  Last_Transaction_Date  BETWEEN  @Prompt('Start Date','D')  AND  @Prompt('End Date','D')

  GROUP BY

  Customer,

  Last_Transaction_Date) A

  Where B.Customer = A.Customer And B.Last_Transaction_Date = A.Last_Transaction_Date

  GROUP BY  A.Customer

Then in the Business, Layer you create objects Customer,, Last Transaction Date and Amount based on the derived table.

And you will have only one row for a customer corresponding to the Max Last_Transaction_Date including on the dates range selected in the 2 @Prompt.

Didier

1 View this answer in context
Not what you were looking for? View more on this topic or Ask a question