Skip to Content

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 DateCustomerMaterialAmount20160105AX1020160121AX2020160210AX3020160225AX4020160304AX5020160316AX6020160103BX7020160108BX8020160124BX9020160206BX10020160215BX11020160218BX120

I wanted to have this query:

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


CustomerLast Transaction DateAmountA2016022540B20160218120

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


CustomerLast Transaction DateAmountA2016012120B2016012490

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

5 Answers

  • Best Answer
    Apr 07, 2016 at 09:49 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 12, 2016 at 09:50 AM

    Hi,

    Thanks a lot guys for the help especially to @Didier MAZOUE and @Narashimman K S..

    @Didier MAZOUE

    I also found another approach similar to yours by using the ROW_NUMBER() OVER (PARTITION BY <fields> ORDER BY <fields>) command..😊

    @Narashimman K S

    Thanks for the trick..😉

    It did also solve my scenario but the only problem I encountered is the PERFORMANCE..I am playing with several data here so I think that's the only downside but it's the easiest way for smaller size data..😊

    Regards,

    Loed

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 10, 2016 at 05:07 PM

    Delegate!

    Create two delegated measures with MAX as the aggregation function. No other SQL/Derived Table required.

    Try Following the screenshots.


    1.jpg (70.3 kB)
    2.jpg (64.5 kB)
    3.jpg (97.7 kB)
    4.jpg (100.2 kB)
    5.jpg (109.8 kB)
    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      HI Narashiman,

      Very nice trick . Can you explain what will happen if we keep database delegate ?

      what will happen actually backend?

      Any help highly appreciated

      Thanks,

      Vaurn

  • avatar image
    Former Member
    Apr 07, 2016 at 08:27 AM

    Hi Loed,

    Try this,

    1. Edit the query and apply a prompt on <Transaction date> object.

    2. Choose the operator as "Between". This will give two parameter fields: start and end.

    3. As it is a date prompt, user should get calendar option to choose dates as far as i am sure.

    4. User can choose start date as 20160201 & end date as 20160229 and run the query.

    Regards,

    Yuvraj

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Loed Despuig

      Hi Leod,

      Can you try creating the max(Transaction Date) object and use it in the report level.

      Unlike sql query the webi will form the query like below. So just give a simple try

      SELECT

      max(Outlet_Lookup.Date_open),

      sum(Shop_facts.Amount_sold),

      Outlet_Lookup.State

      FROM

      Outlet_Lookup,

      Shop_facts

      WHERE

      ( Outlet_Lookup.Shop_id=Shop_facts.Shop_id )

      AND

      Outlet_Lookup.Date_open BETWEEN @Prompt('Enter value for Opening date (Start):','A','Store\Store details\Opening date',Mono,Free,Persistent,,User:0) AND @Prompt('Enter value for Opening date (End):','A','Store\Store details\Opening date',Mono,Free,Persistent,,User:1)

      GROUP BY

      Outlet_Lookup.State

  • avatar image
    Former Member
    Apr 07, 2016 at 04:19 PM

    HI Loed,

    May I know Why you want in IDT

    You can achieve in Webi report level .by keeping Transcation date in query filter and applying prompt and after that we can apply user response

    Correct me if i am wrong.

    Thanks,

    Varun

    Add comment
    10|10000 characters needed characters exceeded

    • Hi,

      I think you have similar idea with Yuvraj above? But that is not the problem..I need to get the latest transaction date of each customer based on the parameter used by the user..

      Regards,

      Loed