Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

rbartley
Contributor III

5 Consecutive Years In Load Script

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

 

COUNTRYTIME_PERIODDATA
SI1995
SI199661.297
SI1997
SI1998
SI1999
SI2000
SI2001
SI2002
SI2003
SI2004
SI200567.115
SI200667.651
SI200768.652
SI200869.153
SI200968.649
SI201067.4
SI201165.064
SI201264.115
SI201364.046
SI201464.912
SI201565.546
SI201666.361
SI2017
SI2018

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?

1 Solution

Accepted Solutions
mikaelsc
Contributor III

Re: 5 Consecutive Years In Load Script

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;

2 Replies
mikaelsc
Contributor III

Re: 5 Consecutive Years In Load Script

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
Contributor III

Re: 5 Consecutive Years In Load Script

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.