topic Re: Data mismatch in excel and csv output in QlikView

Data mismatch in excel and csv output

Thu, 26 Jun 2014 11:11:15 GMT

Hi All

I observed that the same data when exported to csv and excel gives different summary results. This is because excel stores the decimals whereas csv doesnot.

Is there a way to handle this ? As it could be a great problem if users choose to export it to csv.

Please advise.

Re: Data mismatch in excel and csv output

ashfaq_haseeb — Thu, 26 Jun 2014 12:12:01 GMT

Hi,

It worked like a char I tried with 11.2 sr6.

What version of Qlik you are on?

try by upgrading to 11.2 sr6.

Look at the attached files.

Regards

ASHFAQ

Re: Data mismatch in excel and csv output

Thu, 26 Jun 2014 12:15:49 GMT

version 11.20.11643.0 IR

Re: Data mismatch in excel and csv output

ashfaq_haseeb — Thu, 26 Jun 2014 12:22:17 GMT

Please upgrade it to 11.2 sr6

Regards

ASHFAQ

Re: Data mismatch in excel and csv output

Fri, 27 Jun 2014 07:07:17 GMT

Ashfaq

Are you showing decimals on your dashboard?

This is how my data is -

The above data set, when exported to excel and csv has a diffrerence in the sum total as csv doesnot store decimals and excel does.

Re: Data mismatch in excel and csv output

ashfaq_haseeb — Fri, 27 Jun 2014 19:04:59 GMT

Hi,

Yes, I have decimals too. I attached a sample file even.

If possible can you post your .qvw file here.

Regards

ASHFAQ

Re: Re: Data mismatch in excel and csv output

Mon, 30 Jun 2014 12:24:43 GMT

Ashfaq

Please refer to the attachments.

The actual total is $12398.04

But when I export to csv , it sums up to $12398 due to rounding off.

Re: Re: Re: Data mismatch in excel and csv output

ashfaq_haseeb — Mon, 30 Jun 2014 12:53:32 GMT

Hi,

Try below format.

$#,##0.00;($#,##0.00)

Look at the attached document.

Regards

ASHFAQ

Re: Re: Re: Data mismatch in excel and csv output

Mon, 30 Jun 2014 13:05:00 GMT

As mentioned earlier, the requirement is not to show decimals in the dashboard.

Re: Data mismatch in excel and csv output

rwunderlich — Mon, 30 Jun 2014 13:06:08 GMT

This appears to be the way it works -- but impacted by the User Export settings.

For Excel, both the internal raw value and the format is exported, as Excel has the capacity to hold both.

CSV can only hold a single value, no formatting info. "Setting, User Preferences, Export, Number Formatting" controls what is exported to CSV. If you set it to "No Formatting", the raw value is exported. Otherwise the formatted display value is exported.

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Re: Data mismatch in excel and csv output

Mon, 30 Jun 2014 13:12:42 GMT

Thanks Rob. This really helps.

Just to clarify, if I make this setting.... it will be applicable for all users, right?

Re: Data mismatch in excel and csv output

ashfaq_haseeb — Mon, 30 Jun 2014 13:19:34 GMT

Rob's solution works and it it will be applied to all.

Regards

ASHFAQ

Re: Data mismatch in excel and csv output

rwunderlich — Tue, 01 Jul 2014 04:55:14 GMT

My understanding is that this a per user User Setting. Each user must set it. There is no way to set it as a document property.

-Rob

Re: Data mismatch in excel and csv output

Tue, 01 Jul 2014 05:01:51 GMT

Thanks Rob. If users are viewing the dashboard from Access Point then how will they set it ?

Re: Data mismatch in excel and csv output

rwunderlich — Tue, 01 Jul 2014 12:50:05 GMT

Good question. If using the IEPlugin, User Settings can be accessed from the Menu button. There is no equivilent item for Ajax Ajax always seems to export the formatted version. Maybe someone knows a workaround?

-Rob