Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
How do you define close? Is it less than a year?
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
Hi anbu,
By saying "close" I mean the latest date A where A<B, it's not have to be less than one year.
Then use this expression
If(DtB - DtA>0 ,DtB - DtA)
Can you explain more in details about near values for date and what is mean for closest A to B and A<B.
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..
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;