topic Re: Top 1 in QlikView

Top 1

Anonymous — Thu, 12 May 2016 07:34:12 GMT

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.

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

Re: Top 1

MK_QSL — Thu, 12 May 2016 07:58:13 GMT

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;

Re: Top 1

Anonymous — Thu, 12 May 2016 08:04:42 GMT

Hi Manish, Thanks for the help

This just gives a max date for all records,

I wanted the last change per transaction date

Re: Top 1

MK_QSL — Thu, 12 May 2016 08:06:48 GMT

I wanted the last change per transaction date

Not understood this !

Re: Top 1

Anonymous — Thu, 12 May 2016 08:11:07 GMT

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

Re: Top 1

qlikview979 — Thu, 12 May 2016 08:14:13 GMT

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;

Re: Top 1

MK_QSL — Thu, 12 May 2016 08:24:35 GMT

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?

Re: Top 1

Anonymous — Thu, 12 May 2016 08:25:07 GMT

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

Re: Top 1

Anonymous — Thu, 12 May 2016 08:27:23 GMT

Each Transaction date has 3 or More LastchangeDates

As seen above

I just want the last date before the transaction date

Thanks

Re: Top 1

MK_QSL — Thu, 12 May 2016 08:28:50 GMT

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

Re: Top 1

Anonymous — Thu, 12 May 2016 08:43:20 GMT

The Output is the column Date i need

Re: Top 1

MK_QSL — Thu, 12 May 2016 08:44:02 GMT

Can you provide output here!

Re: Top 1

Anonymous — Thu, 12 May 2016 08:45:04 GMT

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

Re: Top 1

MK_QSL — Thu, 12 May 2016 08:47:31 GMT

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;

Re: Top 1

MK_QSL — Thu, 12 May 2016 08:49:10 GMT

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)

!!!!!!

Re: Top 1

Anonymous — Thu, 12 May 2016 08:53:40 GMT

try i this way

maxdate:

load

max(lastchagedate) as maxdate

let vmaxdate=peek(maxdate,0,maxdate)

Re: Top 1

Anonymous — Thu, 12 May 2016 08:58:14 GMT

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

];

Re: Top 1

Anonymous — Thu, 12 May 2016 08:58:31 GMT

Still not working

Re: Top 1

MK_QSL — Thu, 12 May 2016 08:59:20 GMT

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)

!!!!!!

Re: Top 1

Anonymous — Thu, 12 May 2016 09:08:05 GMT

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