topic Re: Send to Excel issue in QlikView

Send to Excel issue

Tue, 20 Dec 2011 05:48:07 GMT

Hi,

I am trying to export a table into excel but somehow few records are missing.

Suppose there are 5000 records, 4950 records are getting exported. I verified this issue by counting records and taking sum of numeric field also. I am using QV 10 and office 2010.

Please suggest.

Mayank

Send to Excel issue

Miguel_Angel_Baeyens — Tue, 20 Dec 2011 06:22:05 GMT

Hi Mayank,

Is that a straight or pivot table? If so, did you try exporting a table box instead? Note that in a chart like the pivot table, some dimension values may not have their corresponding aggregated values (i. e.: sum for the selectin made is equal to zero or null), so they don't appear in the chart, and they are not exported.

Assuming that you are not doing any WHERE clause in your load script, amd that all values are being loaded into QlikView, Check as well that you are not using the "Suppress when value is null" tick in your chart properties, Dimensions tab.

Hope that helps.

Miguel

Re: Send to Excel issue

Tue, 20 Dec 2011 06:31:01 GMT

Hi Miguel,

Thanks for responding.

I am just using Table Box with no charts.

I sum hours field using Sum(Hours) and it summed up to 5286.6. When I sent it to excel, sum in excel shows 5284.6!!

Mayank

Send to Excel issue

vijay_iitkgp — Tue, 20 Dec 2011 07:11:14 GMT

Hi Mayank,

How do you use Sum(Hours) in Table box ???

Re: Send to Excel issue

Tue, 20 Dec 2011 07:13:04 GMT

I didn’t sum(Hours) in Tablebox. I used Text field to get the sum.

Mayank

Send to Excel issue

vijay_iitkgp — Tue, 20 Dec 2011 07:16:45 GMT

Then There must be some duplicating rows in your table . By default qlikview use distinct rows in tablebox. you can create rowNo() as one extra field and use it in table box. Hope this will solve your problem.

Regards

VIjay

Send to Excel issue

Miguel_Angel_Baeyens — Tue, 20 Dec 2011 07:25:07 GMT

Hi Mayank,

That difference may be caused by rounding the decimal positions in both QlikView and Excel. If amounts, quantities and lines show the same, the should sum the same.

Is that possible?

Miguel

Re: Send to Excel issue

Tue, 20 Dec 2011 09:03:37 GMT

You hit the nail buddy. Thanks a lot!

Re: Send to Excel issue

Tue, 20 Dec 2011 09:09:01 GMT

Okay. But how can I hide RowNo() so that it shouldn’t be displayed in the table though it should work as it is? Right now, my table contains RowNo as a field which looks irrelevant.

Mayank

Send to Excel issue

vijay_iitkgp — Tue, 20 Dec 2011 09:37:39 GMT

You can use Straight Table instead of Table box with expression 1 and hide the rowno and expression.

Regards

Vijay

Re: Send to Excel issue

Tue, 20 Dec 2011 11:29:36 GMT

I worked.

Thanks again,