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

[MK_QSL]

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

Now where is 22/06/2016 for 02/02/2015?

[Anonymous]

Look at Data 2nd Row?

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

[MK_QSL]

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


];


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]

It is 22/06/2015 not 22/06/2016.. Check the year!

22/06/2016

[Anonymous]

okay that was a typo i should have said you have 22/06/2015 but this is still wrong?

Leave it i am working on it

[MK_QSL]

This is the output I am getting for your script... Looks like you are doing something wrong at your end...

Script is as below...

Test:

Load * Inline

[

  Account, TransactionDate, ID, LastIDChangeDate

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

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

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

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

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

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

];

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; 

[Anonymous · Accepted solution]

Managed to fix this by the following

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 

]; 

TEST2: 

Load

Account,

TransactionDate,

ID,

LastIDChangeDate,

if(LastIDChangeDate >= TransactionDate, 'Null',LastIDChangeDate) as New

Resident Test; 

Drop Table Test;

Test3:

Load

Account,

TransactionDate,

//ID,

1 as DCF,

max(New) as New

Resident TEST2

group by Account,TransactionDate; 

Drop Table TEST2;

[MK_QSL]

@adil.hussain

So you mean to say my answer was not working ?

You said you want this output..

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

As per your script the final output is not having ID and LastIDChangeDate?

Actually you was asking something different and wanting something different..

Anyway as far as you manage to get answer... I am happy..

But request you to raise question properly based on what exactly you want.. You can understand that how much time I have given for your solution and at last came to know that your QUESTION was different than your EXPECTATION !