topic Re: Export to Excel__urgently require a solution in QlikView

Export to Excel__urgently require a solution

Fri, 25 Oct 2013 14:14:22 GMT

Hello Community,

I have a straight table with ~9 million rows. I can not allow the user to export to excel with so many rows.

So, my requirement is that the user gets the option to export to excel only when the number of rows comes below ~1 million.

I also want to show the dynamically updated number of rows present in the table at any instant. So that the user is aware when he/she will be able to export data and start making further selections to reduce the number of rows.

Kindly help me as I have an urgent requirement and I am in a fix.

The bold comment is more important.

Thanks and Regards,

Vishal

Re: Export to Excel__urgently require a solution

Fri, 25 Oct 2013 14:24:58 GMT

Hi Vishal,

This post from Qlikview Maven could help:

QlikView Maven: Count of Rows in a Chart

Kind Regards,

Dave

Re: Export to Excel__urgently require a solution

marcus_sommer — Fri, 25 Oct 2013 14:45:16 GMT

You need to count the number of rows - count(Field) - within the caption th show the number instantly and count this Field(s) as visibility properties from a additionally export-button and remove the xl-export icon from the caption. But your users will be also able to export to excel per context-menu per right click - this is always possible unless export will be denied, but this is global for the document.

- Marcus

Re: Export to Excel__urgently require a solution

javier_florian — Fri, 25 Oct 2013 14:49:19 GMT

You can export through STORE function:

My_table_20M_rows:

Select * From Table1;

STORE My_table_20M_rows INTO export_file.csv (csv);

Try with this...

Re: Export to Excel__urgently require a solution

Peter_Cammaert — Fri, 25 Oct 2013 14:50:04 GMT

Urgent question, quick hack.

Part one, show number of rows in an UI table, is easy.

Create a macro, let it count the number of rows in your straight table and let it assign that count to

a variable called vNumOfRowsInMyTable. See Re: how can i store a total no of rows of a straight table in a variable? for example code.

Attach that macro to a Document Event Trigger called "OnAnySelect". Whatever selection changes the content of your table, will also update the variable.

Use the variable in your Caption.

Part two (enable/disable Export to Excel) will be pretty difficult as you cannot manipulate UI items using macros, and you cannot Export to an .XLS file within the AccessPoint.

Peter

Re: Export to Excel__urgently require a solution

Jason_Michaelides — Fri, 25 Oct 2013 15:04:31 GMT

You shouldn't even be showing a table with 9 million rows in it!! What good is that to anyone!? Put and Show and calculation condition on the chart itself to only display on a few hundred thousand then they will only be able to export that anyway.

Re: Export to Excel__urgently require a solution

Fri, 25 Oct 2013 15:10:22 GMT

Thank you for all your responses.

Sorry I forgot to mention a few limitations.

I can not write a macro in the tool as in my case it is not allowed. The user has restricted me from using macros.

Secondly can I use count(distinct dim1 & dim2 & dim 3...) to calculate the number of rows?. I have 8 dimensions.

The problem with this is the number of rows automatically updates when I make selections but when I clear all selections it returns ' - ' which is a null value.

Also I tried to cover the table with a transparent text box(without any action) so that the user is not able to export using right click. The send to export button is also covered with a transparent text box which will show when the number of rows is greater than 1 million. When the number of rows is less than 1 million it will hide and the user can select the send to excel icon or right click and export.

But due to the problem above which is written in bold, keeping the count in layout(conditional show) is creating a problem.

@Jason-- I know it is not good to have 9 million rows in a table, but if the user has such a requirement I don not have any option.

Please revert back with suggestions

Thanks and Regards,

Vishal

Re: Export to Excel__urgently require a solution

Fri, 25 Oct 2013 15:12:49 GMT

What is Table 1??? Is it the Object ID??

Re: Export to Excel__urgently require a solution

Jason_Michaelides — Fri, 25 Oct 2013 15:24:41 GMT

There's always an option, Vishal. I would probably throttle anyone I found covering up things with a text box to stop the user doing something rather than challenging requirements and coming to a compromise.

You should always challenge your users on their requirements as they will ask for the moon on a stick. I say again - what good is a table chart with 9 million rows in it? If they get really difficult, ask them for a blank cheque for immense hardware, that usually does the trick. You have to protect the environment for all users and allow for growth.