topic Re: Question about the record selection in QlikView

Question about the record selection

Mon, 21 Apr 2014 09:14:26 GMT

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!

Re: Question about the record selection

anbu1984 — Mon, 21 Apr 2014 09:18:55 GMT

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

Re: Question about the record selection

pgrenier — Mon, 21 Apr 2014 09:22:13 GMT

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

Re: Question about the record selection

Mon, 21 Apr 2014 09:25:36 GMT

Hi anbu,

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

Re: Question about the record selection

anbu1984 — Mon, 21 Apr 2014 09:27:51 GMT

Then use this expression

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

Re: Question about the record selection

its_anandrjs — Mon, 21 Apr 2014 09:31:40 GMT

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

Re: Question about the record selection

Mon, 21 Apr 2014 09:44:06 GMT

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..

Re: Question about the record selection

maxgro — Mon, 21 Apr 2014 09:54:54 GMT

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;