3 Replies Latest reply: Sep 28, 2016 7:28 AM by nagarjuna k RSS

    Load only last 6 periods

    Niko Suomi

      Hello,

       

      I have a bit of a problem, that sounds easy - but I have not yet figured out how to do it properly in QlikView script editor.

       

      I have periods something like this in my data:

      2016-03-16-2016-03-31

      2016-04-01-2016-04-15

      2016-04-16-2016-04-30

      2016-05-01-2016-05-15

      2016-05-16-2016-05-31

      2016-06-01-2016-06-15

      2016-06-16-2016-06-30

      2016-07-01-2016-07-15

      2016-07-16-2016-07-31

      2016-08-01-2016-08-15

      2016-08-16-2016-08-31

       

      And we are only interested of loading the data with the latest 6 period.

      I have kind a managed to do that with my example Qlik-app, but it does not have distinct values on the filtering table. And it causes the massive data amount to not to load correctly. With this size of sample data, it goes through just fine, but not in real world scenario.

       

      So, we have these periods.

      We load them with the data we have, at first they are separately so they have been concatenated to one field called "period".

      From these periods, we want to show and maybe also to load the six latest ones, that in this case are the following periods:

      2016-06-01-2016-06-15

      2016-06-16-2016-06-30

      2016-07-01-2016-07-15

      2016-07-16-2016-07-31

      2016-08-01-2016-08-15

      2016-08-16-2016-08-31

       

      So, the data from these periods only and everything else can be left out.

      But when I load the data, it gets 18 rows for the table3 where i should only have distinct period values.Why isn't this working as the way I want?

       

      There might be a better way to do this than the one I have on my QV app at the moment, and suggestions can be left here also

      If you guys have any idea how to handle this, it would be really lovely to know!

       

       

       

      Cheers,

      Niko

        • Re: Load only last 6 periods
          nagarjuna k

          Hi,

           

          Like this ?

           

          Use the below code and use Flag field to filter ur required data .

           

           

          table:

           

           

          Load * inline

           

           

          [

           

           

          period, arvo

          2015-12-01-2015-12-15, 1000

          2015-12-16-2015-12-31, 1000

          2016-01-01-2016-01-15, 1000

          2016-01-16-2016-01-31, 1000

          2016-02-01-2016-02-15, 1000

          2016-02-16-2016-02-29, 1000

          2016-03-01-2016-03-15, 1000

          2016-03-16-2016-03-31, 1000

          2016-04-01-2016-04-15, 1000

          2016-04-16-2016-04-30, 1000

          2016-05-01-2016-05-15, 1000

          2016-05-16-2016-05-31, 1000

          2016-06-01-2016-06-15, 1000

          2016-06-16-2016-06-30, 1000

          2016-07-01-2016-07-15, 1000

          2016-07-16-2016-07-31, 1000

          2016-08-01-2016-08-15, 1000

          2016-08-16-2016-08-31, 1000

          2015-12-01-2015-12-15, 1000

          2015-12-16-2015-12-31, 1000

          2016-01-01-2016-01-15, 1000

          2016-01-16-2016-01-31, 1000

          2016-02-01-2016-02-15, 1000

          2016-02-16-2016-02-29, 1000

          2016-03-01-2016-03-15, 1000

          2016-03-16-2016-03-31, 1000

          2016-04-01-2016-04-15, 1000

          2016-04-16-2016-04-30, 1000

          2016-05-01-2016-05-15, 1000

          2016-05-16-2016-05-31, 1000

          2016-06-01-2016-06-15, 1000

          2016-06-16-2016-06-30, 1000

          2016-07-01-2016-07-15, 1000

          2016-07-16-2016-07-31, 1000

          2016-08-01-2016-08-15, 1000

          2016-08-16-2016-08-31, 1000

          2015-12-01-2015-12-15, 1000

          2015-12-16-2015-12-31, 1000

          2016-01-01-2016-01-15, 1000

          2016-01-16-2016-01-31, 1000

          2016-02-01-2016-02-15, 1000

          2016-02-16-2016-02-29, 1000

          2016-03-01-2016-03-15, 1000

          2016-03-16-2016-03-31, 1000

          2016-04-01-2016-04-15, 1000

          2016-04-16-2016-04-30, 1000

          2016-05-01-2016-05-15, 1000

          2016-05-16-2016-05-31, 1000

          2016-06-01-2016-06-15, 1000

          2016-06-16-2016-06-30, 1000

          2016-07-01-2016-07-15, 1000

          2016-07-16-2016-07-31, 1000

          2016-08-01-2016-08-15, 1000

          2016-08-16-2016-08-31, 1000

          ];

           

           

          t1:

          LOAD Distinct

          Date(Left(period,10)) as Date,

          period

           

           

          Resident table  ;

           

           

          DROP Table table ;

           

           

           

           

          t2:

          LOAD *,

          RowNo() as ID

          Resident t1 Order by Date desc ;

          DROP Table t1 ;

           

           

          NoConcatenate

          Final:

           

           

          LOAD * ,

          'Flag' as Flag

          Resident t2 Where ID<=6 ;

           

           

          I hope this may help u :-)

           

           

          Regards,

          Nagarjun