Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;
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.