5 Consecutive Years In Load Script

[rbartley]

Hi everyone,

I have been trying to work out how to create a flag in the Qlik Sense load script that will show the earliest year for which 5 years' consecutive data is available.

The fields in the table are as follow:

COUNTRY (e.g UK,FR,US)

TIME_PERIOD (year, e.g.2000)

DATA (e.g. 10,000)

Here's an example for Slovenia

COUNTRY	TIME_PERIOD	DATA
SI	1995
SI	1996	61.297
SI	1997
SI	1998
SI	1999
SI	2000
SI	2001
SI	2002
SI	2003
SI	2004
SI	2005	67.115
SI	2006	67.651
SI	2007	68.652
SI	2008	69.153
SI	2009	68.649
SI	2010	67.4
SI	2011	65.064
SI	2012	64.115
SI	2013	64.046
SI	2014	64.912
SI	2015	65.546
SI	2016	66.361
SI	2017
SI	2018

As you can see, there's data for 1996, but then no more until 2005, which is the earliest year with 5 years' consecutive data and so I would want a field which shows 2005 against each record.

I have been able to do this in a query using the Oracle LAG function, but not using the Qlik LOAD script.  Does anyone have any ideas?

[mikaelsc · Accepted solution]

something like this, using peek() function (interrecord)

*notice the order by desc...

test:

load * inline [

COUNTRY, TIME_PERIOD, DATA

SI, 1995,

SI,1996, 61.297

SI, 1997,

SI, 1998,

SI, 1999,

SI, 2000,

SI, 2001,

SI, 2002,

SI, 2003,

SI, 2004,

SI, 2005, 67.115

SI, 2006, 67.651

SI, 2007, 68.652

SI, 2008, 69.153

SI, 2009, 68.649

SI, 2010, 67.4

SI, 2011, 65.064

SI, 2012, 64.115

SI, 2013, 64.046

SI, 2014, 64.912

SI, 2015, 65.546

SI, 2016, 66.361

SI, 2017,

SI, 2018,

];

temp:

load

COUNTRY,

    TIME_PERIOD,

    DATA,

    if(len(DATA)>0 and len(peek(DATA,-1))>0 AND len(peek(DATA,-2))>0 and len(peek(DATA,-3))>0 and len(peek(DATA,-4))>0 and len(peek(DATA,-5))>0,1,0) as _toconsiderDOWN

resident test

order by TIME_PERIOD desc;

left join(test)

load

COUNTRY,

    min(TIME_PERIOD) as MinDateWith5ConseqYears

resident temp

where [_toconsiderDOWN]=1

group by COUNTRY;

drop table temp;

[rbartley]

Hi Mikael,

Thanks for the response.  That works very well and much better than the idea I was playing with of creating 5 different temp tables.  I've used peek() before to assign results to a variable but it never occurred to me to use it in this way.