topic Re: Export to excel_urgent in QlikView

Export to excel_urgent

Mon, 21 Oct 2013 14:27:19 GMT

Hello Community,

I have 20 million rows in a straight table. When I export to excel my QVW file stops responding.

Also the performance and response time is not user friendly. The tool size is 436 MB and I, now, have to add this straight table.

I know Excel cannot handle so much data. Can anyone give me a way about this?

Also, Can anyone suggest me a checklist so that I could improve the performance. Exporting to excel is high priority for me. Please reply asap...

Thanks,

Vishal

Re: Export to excel_urgent

javier_florian — Mon, 21 Oct 2013 14:31:06 GMT

You can create a store...

STORE Table_Name INTO file_name.qvd (qvd)

STORE Table_Name INTO file_name.txt (txt)

Re: Export to excel_urgent

Mon, 21 Oct 2013 14:35:06 GMT

Hey Javier,

Can you explain a little further please?

I had a Data base and converted them into QVD. Now, I am using the QVD's to populate my straight table... The straight table has 20 million rows in QlikView front end... So I don't know a way around this.

Kindly reply

Regards,

Vishal

Re: Export to excel_urgent

javier_florian — Mon, 21 Oct 2013 14:41:09 GMT

Hi Vishal,

Do you need export >20M rows, you server will shut down... so, if you used a STORE to export data, your performance doesn't decrease... let me know if it work for you

Re: Export to excel_urgent

Mon, 21 Oct 2013 14:45:26 GMT

Hey Javier,

Thanks for your time! I have one question.

In the script,

STORE Table_Name INTO file_name.txt (txt)

What would be the table name? Do you mean the table ID??? and can it be stored as csv???

I am sorry. I am a newbie

Re: Export to excel_urgent

javier_florian — Mon, 21 Oct 2013 14:53:12 GMT

For example:

ODBC ....

Table_1:

SELECT ...

FROM ...;

Table_2:

SELECT...

FROM ...;

STORE Table_1 INTO my_file.csv (csv)

STORE Table_2 INTO other_file_name.csv (csv)

Re: Export to excel_urgent

Mon, 21 Oct 2013 15:08:33 GMT

thank you Javier

Re: Export to excel_urgent

Mon, 21 Oct 2013 15:15:43 GMT

In my understanding if you are using a straight table to show your information, no calculations are made so you can fit your model in order to create using "join" or "mapping" or "lookup" a joined table in qlikview memory and then throw store statement to store it in CSV. If you provide us with your data model I could be more specific.

Re: Export to excel_urgent

javier_florian — Mon, 21 Oct 2013 15:24:59 GMT

If you work with big data, performance could decrease on your server... In straight table, you can export data directly, but it can impact your performance

Re: Export to excel_urgent

Tue, 22 Oct 2013 06:43:36 GMT

I didn't comment performance aspect. This is the reason because I said to make export in qlikview script instead of in an object. In script performance issues could be solved easier.

Re: Export to excel_urgent

Tue, 22 Oct 2013 06:53:39 GMT

Hey Victor,

How do I export in QlikView script?

Do you have a code or script for that?

Considering I have a table with Object ID (for eg:) 'ST12'

Re: Export to excel_urgent

Tue, 22 Oct 2013 07:05:11 GMT

You export using STORE instruction but you can only export to CSV format not XLS or XLSX

Re: Export to excel_urgent

Tue, 22 Oct 2013 07:14:53 GMT

I need an example of your current data model to give you more information.

Re: Export to excel_urgent

Tue, 22 Oct 2013 07:34:17 GMT

Sorry Victor it is difficult to provide the data model.., I have nine dimensions in the table and six expressions. I want to export the contents of the table.

I hope this info is useful

Regards,

Vishal

Re: Export to excel_urgent

Tue, 22 Oct 2013 07:41:46 GMT

Then you aren't using a Straight Table , but a Simple Table Chart. Without the data model it's dificult to tell you but I think you should make calculations in script rather than in an object to improve performance. You must aggregate calculations in this way

LOAD DIM1,DIM2,DIM3,DIM4,DIM5,DIM6,DIM7,DIM8,DIM9, CALC1,CALC2,-----,CALC6

GROUP BY DIM1,DIM2,DIM3,DIM4,DIM5,DIM6,DIM7,DIM8,DIM9;

IF some selection is made to achieve the table you must apply WHERE filter.

ALL DIMS and FIELDS must be in one table.

Then store to CSV.