Difference in Date

Thu, 17 Jul 2014 19:24:55 GMT

Hi All

I have a requirement , where i have to compare the STMNT_DT ,

If the Period type = Year End , for each nick name i should compare the STMNT_DT

If the date difference is > 730 days when comparing STMNT_DT witht the previous STMNT_DT record, i should display those Nick name records.

Thanks

Senthil

Re: Difference in Date

MK_QSL — Thu, 17 Jul 2014 19:53:29 GMT

Temp:

LOAD *, NCKNM&AGENT&COUNTRY&UNIT as KEY Inline

[

NCKNM, AGENT, COUNTRY, UNIT, PERIOD TYPE, STMNT_DT

100, ABC, ABC, UBIT, 6 Months, 31/06/2000

100, ABC, ABC, UBIT, Year End, 31/12/2000

100, ABC, ABC, UBIT, Year End, 31/12/2001

100, ABC, ABC, UBIT, Year End, 31/12/2003

100, ABC, ABC, UBIT, Year End, 31/12/2004

100, ABC, ABC, UBIT, 6 Months, 31/12/2000

100, ABC, ABC, UBIT, Year End, 31/12/2007

100, ABC, ABC, UBIT, Year End, 31/12/2005

100, ABC, ABC, UBIT, Year End, 31/12/2006

100, ABC, ABC, UBIT, Year End, 31/12/2009

100, ABC, ABC, UBIT, Year End, 31/12/2008

100, ABC, ABC, UBIT, Year End, 31/12/2010

101, ABC, ABC, UBIT, 6 Months, 31/06/2000

101, ABC, ABC, UBIT, Year End, 31/12/2000

101, ABC, ABC, UBIT, Year End, 31/12/2001

101, ABC, ABC, UBIT, Year End, 31/12/2003

101, ABC, ABC, UBIT, Year End, 31/12/2004

101, ABC, ABC, UBIT, 6 Months, 31/12/2000

101, ABC, ABC, UBIT, Year End, 31/12/2007

101, ABC, ABC, UBIT, Year End, 31/12/2005

101, ABC, ABC, UBIT, Year End, 31/12/2006

101, ABC, ABC, UBIT, Year End, 31/12/2009

101, ABC, ABC, UBIT, Year End, 31/12/2008

101, ABC, ABC, UBIT, Year End, 31/12/2010

];

Temp2:

Load

IF(KEY = Previous(KEY) and [PERIOD TYPE] = 'Year End' , STMNT_DT - Previous(STMNT_DT)) AS Difference

Resident Temp

Order By KEY, STMNT_DT ASC;

Drop Table Temp;

NoConcatenate

Temp3:

Load

NCKNM,

If(NOT ISNULL(Difference) and Difference >= 730, 'Yes','No') as Flag,

AGENT, COUNTRY, UNIT, [PERIOD TYPE], Difference

Resident Temp2;

Drop Table Temp2;

===========================

Now use Flag as your list box and selecting Yes from Flag List Box will give you desired result..

topic Re: Difference in Date in QlikView

Difference in Date

Re: Difference in Date