Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Welcome ! @Niko Suomi
-Nagarjun