Top 1

[Anonymous]

Hi All

I needed help with trying to obtain the lastIDChagedate to a Transactiondate.

Qvw Attached.

Basically i want the last lastIDChagedate against a transaction.

Currently i am getting many.

Eg

On TransactionDate 02/02/2015 The Last LastIDchangedate was 22/06/2007

On TransactionDate 02/07/2015 The Last LastIDchangedate was 22/06/2015

I needed to filter the last ID based on these dates.

In script please not UI

Any ideas please?

Thanks

[Anonymous]

The Output is the column Date i need

[MK_QSL]

Can you provide output here!

[Anonymous]

Account	TransactionDate	ID	LastIDChangeDate	Date i Need
1	02/02/2015	1050	22/06/2007	22/06/2007
1	02/02/2015	1234	22/06/2015	22/06/2007
1	02/02/2015	1423	22/06/2007	22/06/2007
1	02/07/2015	1050	22/06/2007	09/06/2015
1	02/07/2015	1234	09/06/2015	09/06/2015
1	02/07/2015	1423	18/08/2010	09/06/2015

[MK_QSL]

Test:
LOAD * INLINE [
    Account, TransactionDate, ID,    LastIDChangeDate
    1, 02/02/2015,  1050,  22/06/2007
    1, 02/02/2015,  1423,  22/06/2007
    1, 02/02/2015,  1234,  22/06/2015
    1, 02/07/2015,  1050,  22/06/2007
    1, 02/07/2015,  1423,  18/08/2010
    1, 02/07/2015,  1234,  09/06/2015
];


Left Join (Test)
Load 
  Account, 
  TransactionDate,
  Date(Max(LastIDChangeDate)) as MaxDate 
Resident Test
Group By Account, TransactionDate;


Left Join (Test)
Load 
  Account, 
  TransactionDate, 
  MaxDate as RequiredDate 
Resident Test;

[MK_QSL]

Why

22/06/2007 for 02/02/2015 (as 22/06/2007 is lowest date) and

09/06/2015 for 02/07/2015 (as 02/07/2015 is highest date)

!!!!!!

[Anonymous]

try i  this way

maxdate:

load

max(lastchagedate) as maxdate

let vmaxdate=peek(maxdate,0,maxdate)

[Anonymous]

Hi Vsudhakar

Can you give example with the script please?

Thanks

Test: 

LOAD * INLINE [ 

    Account, TransactionDate, ID,    LastIDChangeDate 

    1, 02/02/2015,  1050,  22/06/2007 

    1, 02/02/2015,  1423,  22/06/2007 

    1, 02/02/2015,  1234,  22/06/2015 

    1, 02/07/2015,  1050,  22/06/2007 

    1, 02/07/2015,  1423,  18/08/2010 

    1, 02/07/2015,  1234,  09/06/2015 

]; 

[Anonymous]

Still not working

[MK_QSL]

Why

22/06/2007 for 02/02/2015 (as 22/06/2007 is lowest date) and

09/06/2015 for 02/07/2015 (as 02/07/2015 is highest date)

!!!!!!

[Anonymous]

Account	TransactionDate	ID	LastIDChangeDate	MaxDate	RequiredDate
1	02/02/2015	1050	22/06/2007	22/06/2015	22/06/2015
1	02/02/2015	1234	22/06/2015	22/06/2015	22/06/2015
1	02/02/2015	1423	22/06/2007	22/06/2015	22/06/2015
1	02/07/2015	1050	22/06/2007	09/06/2015	09/06/2015
1	02/07/2015	1234	09/06/2015	09/06/2015	09/06/2015
1	02/07/2015	1423	18/08/2010	09/06/2015	09/06/2015

For TransactionDate 02/02/2015 you have requireddate as 22/06/2016

But this date did not exist on 02/02/2015, The LastIDChangeDate was 22/06/2007

I want the last LastIDChangeDate for a Transaction so if the LastIDChangeDate  is more than Transaction date ignore this one and give me the max LastIDChangeDate  before the Transactiondate

Hope this makes more clearer