topic Re: Limit the Number of Rows on Export to Excel in QlikView

Limit the Number of Rows on Export to Excel

anushree1 — Tue, 04 Sep 2018 08:05:35 GMT

Hi,

I have a straight table showing around 100000 rows , but when exporting to excel I would want it to export only 10000 though the qlikview chart displays 100000 rows, I did try using RowLimitForCSVInsteadofExcel setting but it does not seem to work as it exports all the rows even if i set it to 100 and CSV conversion is taking more time as well.

Could someone suggest on implementing this

Re: Limit the Number of Rows on Export to Excel

ogautier62 — Tue, 04 Sep 2018 09:24:01 GMT

load your sample in another table before store :

csv:

first 10000 load *,1 as tmp resident your table ;

drop field tmp;

store csv into file.csv (your csv options) ;

regards

Re: Limit the Number of Rows on Export to Excel

manoj217 — Tue, 04 Sep 2018 09:32:45 GMT

use autogenerate function it will creates unique numbers then use load where condition < x

else

first x load;

Re: Limit the Number of Rows on Export to Excel

ksomosera10 — Tue, 04 Sep 2018 09:33:12 GMT

Hi Anushree,

Good day.

You can use a script to show only the necessary numbers that you want to be extracted to excel.

Kindly provide your sample app so that I can check it.

Thanks!

Re: Limit the Number of Rows on Export to Excel

anushree1 — Tue, 04 Sep 2018 09:53:52 GMT

I do not want to restrict the number of rows to be loaded, i only want to restrict the number of rows exported onto excel, so the suggested approach would not work

Re: Limit the Number of Rows on Export to Excel

anushree1 — Tue, 04 Sep 2018 09:55:37 GMT

writing the script would reduce the rows appearing on qlikview chart as well, i do not want that all i need is though the chart has 1000000 rows when i right click and export to excel it should export only 10000 rows

Re: Limit the Number of Rows on Export to Excel

anushree1 — Tue, 04 Sep 2018 09:56:30 GMT

I want it to work from the front end so that the selections made also has an effect

Re: Limit the Number of Rows on Export to Excel

ksomosera10 — Tue, 04 Sep 2018 10:02:23 GMT

Hi,

I think the Maximum for Excel export is around 1M cells.

If you want to adjust the behavior of the export button itself, maybe you can modify it using Macro.

Thanks!

Re: Limit the Number of Rows on Export to Excel

anushree1 — Tue, 04 Sep 2018 10:05:30 GMT

Could you please share the macro in case you have it handy

Re: Limit the Number of Rows on Export to Excel

ksomosera10 — Tue, 04 Sep 2018 10:09:34 GMT

Hi,

I can put a link for you of someone who also shared his macro on creating an Export limitation.

You can just tweak it for yourself.

Thanks!

Re: Limit the Number of Rows on Export to Excel

jonathandienst — Tue, 04 Sep 2018 10:28:22 GMT

The best way to do this (which does not require a macro), is to either

(1) Use a calculation condition that prevents the export table from being displayed or exported until the user has made sufficient selection to reduce the table

(2) Create a sheet object that only displays 10.000 rows using Dimension Limits or RowNo()

I prefer the former as the first 10.000 rows may not be properly representative and this requires the user to request more specific information which may be more relevant.

Re: Limit the Number of Rows on Export to Excel

anushree1 — Tue, 04 Sep 2018 10:38:56 GMT

Hi Jonathan,

The requirement is to show all the rows on qlikview and export only 10000 rows but using the suggested method I would restrict the number of rows showed on the qlikview as well which is not desirable

Re: Limit the Number of Rows on Export to Excel

jonathandienst — Tue, 04 Sep 2018 10:48:40 GMT

You expect the user to get value from a table containing 100.000 or 1.000.000 rows?

You could of course create a duplicate object with reduced size for export...

Re: Limit the Number of Rows on Export to Excel

aiguo — Thu, 15 Oct 2020 19:34:05 GMT

Were you able to achieve what you were trying to do @anushree1 ? I have a requirement to do something similar.