2 Replies Latest reply: May 17, 2018 3:36 AM by Richard Bartley RSS

    5 Consecutive Years In Load Script

    Richard Bartley

      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?

        • Re: 5 Consecutive Years In Load Script
          Mikael Scorielle

          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;