topic Re: How to avoid duplicates when export to excel? in QlikView

How to avoid duplicates when export to excel?

Mon, 18 Aug 2014 14:24:02 GMT

Hi,

I have one table which have dimension CustomerID with duplicates. for each CustomerID there are multiple unique ID. My user don't want to see duplicates in CustomerID when exported to excel. but wants all CustomerID with ID. so, I used Pivot table. In qvw it is showing only once instance of CustomerID with ID, but when exporting to excel it is showing duplicate CustomerID.

How to sort this out. Can anyone help me please.

Thanks.

Re: How to avoid duplicates when export to excel?

Anonymous — Mon, 18 Aug 2014 14:32:36 GMT

in your other thread for the same requirement, tresco's response which you marked as the helpful answer, is the way to go about your requirement.

Re: How to avoid duplicates when export to excel?

Mon, 18 Aug 2014 14:42:52 GMT

Thanks. I showed the one to the user which I marked as helpful answer. But the user don't want that format. why I marked it as helpful is as others can help from this helpful answer. if any one have this issue and anyone is ok( as it will be different in different organisations) with that format.

Please any other solution.

Re: How to avoid duplicates when export to excel?

Tue, 19 Aug 2014 09:18:13 GMT

Please any solution?

Re: How to avoid duplicates when export to excel?

martynlloyd — Tue, 19 Aug 2014 09:27:48 GMT

Use STORE

Store will allow you to define the output fields, and you can create a CSV file for excel to read. Your user can open the file instead of exporting the pivot.

By using Resident, Distinct, Group By etc. you can build a table for excel, and then DROP it when you have finished:

Store

CustomerID,

MyfieldB as b

from mytable into xyz.csv (txt);

DROP Table mytable

Regards,

Marty.

Re: How to avoid duplicates when export to excel?

ashfaq_haseeb — Tue, 19 Aug 2014 09:27:55 GMT

Hi,

Can you post some sample data to explore.

You need to use macro here.

If you can post sample then I would give it a try.

Regards

ASHFAQ

Re: How to avoid duplicates when export to excel?

martynlloyd — Tue, 19 Aug 2014 09:31:30 GMT

Alternatively, create a Pivot just for export, with the required columns, and leave it minimised. Instruct the user to right click on that control instead of the displayed version.

Regards,

Marty.

Re: How to avoid duplicates when export to excel?

sujeetsingh — Tue, 19 Aug 2014 09:39:22 GMT

It will be good to use some macro but it will be much complex.

Re: How to avoid duplicates when export to excel?

Tue, 19 Aug 2014 09:42:33 GMT

Thanks. Please see attached sample and help me.

Re: How to avoid duplicates when export to excel?

Tue, 19 Aug 2014 09:45:00 GMT

Thanks. I tried this and still showing the duplicates.

Re: Re: How to avoid duplicates when export to excel?

ashfaq_haseeb — Tue, 19 Aug 2014 09:57:04 GMT

Hi,

have a look at attached application and Export.

Let me know if that worked.

Regards

ASHFAQ

Re: How to avoid duplicates when export to excel?

martynlloyd — Tue, 19 Aug 2014 10:03:05 GMT

Did you use DISTINCT or GROUP to create a summarized table?

This should definitely work. Can you describe your data?

Marty.

Re: How to avoid duplicates when export to excel?

Tue, 19 Aug 2014 10:57:24 GMT

Thanks very much. will the macro work if I use this macro in qvw which is in access point? because I have not used any macros in my qvws which are in access point?

Re: How to avoid duplicates when export to excel?

ashfaq_haseeb — Tue, 19 Aug 2014 11:12:45 GMT

Hi,

Yes you have to use IE Pluggin.

Macros will not work on AJAX Client.

If it answered, can you close this thread.

Regards

ASHFAQ

Re: How to avoid duplicates when export to excel?

Tue, 19 Aug 2014 11:30:13 GMT

Thanks. unfortunately we have Ajax client. how can I solve this with Ajax client. Is there any other way?

Re: How to avoid duplicates when export to excel?

ashfaq_haseeb — Tue, 19 Aug 2014 11:46:14 GMT

Hi, Macros does not work with Ajax.

I don't think there is an alternate.

Regards

ASHFAQ

Re: How to avoid duplicates when export to excel?

martynlloyd — Tue, 19 Aug 2014 11:50:40 GMT

Going back to your pivot chart, please can you post the expressions here?

Re: How to avoid duplicates when export to excel?

Tue, 19 Aug 2014 12:33:25 GMT

Thanks. All I have the expression as below

=If(colour<>'out',colour)

=Count(distinct(If(IsNull(Dateproblemresolved),ID)))

CustomerID is used as dimension

Re: How to avoid duplicates when export to excel?

martynlloyd — Tue, 19 Aug 2014 13:40:40 GMT

Ok. Now how is the user exporting to excel,

Is he/she using Export...

Or is he/she using Send to Excel?

Re: How to avoid duplicates when export to excel?

Wed, 27 Aug 2014 09:45:25 GMT

Thanks. User is using send to excel. Please any help.