Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Load only last 6 periods

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

1 Solution

Accepted Solutions
nagarjuna_kotha
Valued Contributor II

Re: Load only last 6 periods

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

3 Replies
nagarjuna_kotha
Valued Contributor II

Re: Load only last 6 periods

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

Not applicable

Re: Load only last 6 periods

Hello Nagarjuna!

This seems like to be the correct solution for this !

I have to test this on the real version and see how it works on there, but on the test QV-app this seems to work just fine.

I would add drop table t2 there also in the end, so it actually filters the last 6 there only. Now it syncs keys with the t2 and Final

Well, it was quite a simple solution after all. Thank you!

Cheers,

Niko

nagarjuna_kotha
Valued Contributor II

Re: Load only last 6 periods

Welcome ! @Niko Suomi

-Nagarjun