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]

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;

[Anonymous]

Hi Manish, Thanks for the help

This just gives a max date for all records,

I wanted the last change per transaction date

[MK_QSL]

I wanted the last change per transaction date

Not understood this !

[Anonymous]

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

[qlikview979]

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;

[MK_QSL]

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?

[Anonymous]

Sorry i think you know how to do this but i am bad at explaining so i have done the follwoing

[Anonymous]

Each Transaction date has 3 or More LastchangeDates

As seen above

I just want the last date before the transaction date

Thanks

[MK_QSL]

Ok.. what is the output you are looking for from above given excel data?