Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Output:
Load
Account,
TransactionDate,
ID,
LastIDChangeDate,
If(LastIDChangeDate=MaxDate,ID) as FlagID
Resident Test;
Drop Table Test;
Hi Manish, Thanks for the help
This just gives a max date for all records,
I wanted the last change per transaction date
I wanted the last change per transaction date
Not understood this !
On TransactionDate 02/02/2015 The Last LastIDchangedate was 22/06/2007
On TransactionDate 02/07/2015 The Last LastIDchangedate was 22/06/2015
On your answer you have 22/06/2015 for all as max date
For 02/02/2015 The Last LastIDchangedate was 22/06/2007 so should be this date
Hi Hussain,
try this
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
];
Load
Account,
TransactionDate,
date(max(LastIDChangeDate)) as [Last LastIDChangeDate],
1 as DCF
Resident Test Group by Account,TransactionDate;
Drop Table Test;
You have provided below data..
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
];
Now you are saying..
On TransactionDate 02/02/2015 The Last LastIDchangedate was 22/06/2007
On TransactionDate 02/07/2015 The Last LastIDchangedate was 22/06/2015
For 02/07/2015, where 22/06/2015?
Sorry i think you know how to do this but i am bad at explaining so i have done the follwoing
Each Transaction date has 3 or More LastchangeDates
As seen above
I just want the last date before the transaction date
Thanks
Ok.. what is the output you are looking for from above given excel data?