topic Help needed in QlikView

Help needed

piekepotloed — Wed, 25 Nov 2020 16:16:04 GMT

This is a small part of my data:

Deelnemer	Peildatum
70208	1-10-2015
70208	1-10-2014
70208	1-10-2013
25245	1-10-2015
25245	1-10-2014
25245	1-10-2012
25245	1-10-2008
25245	1-10-2007
65894	1-10-2015

I need to count the moments in peildatum. If the gap between the years is bigger than 3, the counting is stopped, A missing year between two records, needs to be counted.. So the result I need is in this table.

Deelnemer	Number of years
70208	3
25245	4
65894	1

Who has a solution for me, I ran out of idea's.

Thanks

Re: Help needed

swuehl — Tue, 14 Jun 2016 15:35:59 GMT

How do you get to 4 for 25245?

Re: Help needed

piekepotloed — Wed, 15 Jun 2016 08:06:17 GMT

I've to count the missing year also, if the gap between two years is less than or equal to 3, so that's why i get to 4.

Re: Help needed

swuehl — Wed, 15 Jun 2016 09:02:04 GMT

Ok, maybe something like this?

Set DateFormat = 'D-M-YYYY';


INPUT:
LOAD Deelnemer,
    Peildatum,
    Year(Peildatum) as Peilyear
FROM
[https://community.qlik.com/thread/220801]
(html, codepage is 1252, embedded labels, table is @1);


TMP:
LOAD Deelnemer,
  If(Previous(Deelnemer) <> Deelnemer, 1, Rangesum( Peek('YearCount'), If(Peilyear >= Previous(Peilyear)-2, Previous(Peilyear)-Peilyear))) as YearCount,
  If(Previous(Deelnemer) <> Deelnemer, 0, Rangesum(Peek('StopFlag'),If(Peilyear < Previous(Peilyear)-2,1))) as StopFlag
Resident INPUT
ORDER BY Deelnemer, Peilyear desc;


Result:
LOAD Deelnemer,
  Max(YearCount) as YearCount
Resident TMP
WHERE StopFlag = 0
GROUP BY Deelnemer;


DROP TABLE TMP;

Deelnemer	YearCount
25245	4
65894	1
70208	3

Re: Help needed

piekepotloed — Wed, 15 Jun 2016 09:42:05 GMT

Thanks for your quick response...it works