Question about the record selection

[#1]

Hi guys,

I have a question which needs your help...I have a table below which contains date A and date B. I want to get my final table which only contains the closest A to B and A<B. How to achieve that?

Original table:

ID	date A	date B
1	12/31/2012	9/30/2011
1	3/31/2009	9/30/2011
1	6/30/2010	9/30/2011
2	6/30/2011	4/30/2010
2	5/31/2012	4/30/2010
3	3/31/2008	3/31/2009
3	5/31/2010	3/31/2009
3	7/31/2012	3/31/2009

Final table:

ID	date A	date B
1	6/30/2010	9/30/2011
3	3/31/2008	3/31/2009

Thanks!

[anbu1984]

How do you define close? Is it less than a year?

[pgrenier]

Hello Kitty,

I believe you can achieve your goal with this statement

FinalTable:

LOAD ID, Max([date A]), Max([date B]

Resident OriginalTable

Where [date A] < [date B];

Regards,

Philippe

[#4]

Hi anbu,

By saying "close" I mean the latest date A where A<B, it's not have to be less than one year.

[anbu1984]

Then use this expression

If(DtB - DtA>0 ,DtB - DtA)

[its_anandrjs]

Can you explain more in details about near values for date and what is mean for closest A to B and A<B.

[#7]

Use this script

Test2:

Load * Inline [
ID, date A, date B
1, 12/31/2012, 9/30/2011
1, 3/31/2009, 9/30/2011
1, 6/30/2010, 9/30/2011
2, 6/30/2011, 4/30/2010
2, 5/31/2012, 4/30/2010
3, 3/31/2008, 3/31/2009
3, 5/31/2010, 3/31/2009
3, 7/31/2012, 3/31/2009
];

FinalTable:
NoConcatenate
LOAD *
Resident Test2
Where [date A] < [date B];

Drop Table Test2;

Hope it helps you..

[maxgro]

SCRIPT

SET DateFormat='MM/DD/YYYY';

DIRECTORY;

source:

LOAD

  ID,

    [date A] as A,

    [date B] as B,

    [date B] - [date A] as DIFF

FROM

[http://community.qlik.com/thread/114995]

(html, codepage is 1252, embedded labels, table is @1)

Where [date B] - [date A] >= 365;  

table:

NoConcatenate

load

  *

Resident source

Where (Peek(ID) <> ID)

order by ID, DIFF;

DROP Table source;