topic Re: when I export values to excel from straight table I see additional rows in QlikView

when I export values to excel from straight table I see additional rows

Fri, 28 Feb 2014 20:50:21 GMT

Hi Everyone,

I am trying to export values from pivot table to excel but I end up getting extra rows in my exported excel for example

I have a fields called Project Number and Person_Responsible named Edward who has multiple project numbers,

when I am exporting values from the Pivot Table in qlikview to excel , I see redundant rows in excel like extra project numbers for that person responsible that are not present in the datasource also not present in the pivot table in qlikview

Can someone please provide me some inputs on this, is there a way to resolve this?

Thanks in advance

Re: when I export values to excel from straight table I see additional rows

pover — Sat, 01 Mar 2014 02:31:26 GMT

Hi Anusha,

When you export a Qlik pivot table to Excel no cells will be merged so values will be repeated. There's no real way to get around it.

Karl

Re: when I export values to excel from straight table I see additional rows

Sat, 01 Mar 2014 02:34:53 GMT

Can I use a vb script in a button with run a macro action, if so could you please provide me a script I tried some of the scripts from community nothing helped

Appreciate your help

Re: when I export values to excel from straight table I see additional rows

whiteline — Sat, 01 Mar 2014 07:16:36 GMT

Hi.

Right click -> Copy ->Full table -> Paste into Excel.

This way the cells are merged.

Re: when I export values to excel from straight table I see additional rows

aveeeeeee7en — Sat, 01 Mar 2014 07:42:46 GMT

Hi Anusha

It's a Limitation in Qlikview.

You can use Macro for this thing

or As Whiteline said, you can simply right click on Any chart > Click on Copy to Clipboard > Full Table > Open Excel sheet and paste the copied Table. After doing this you can do table formatting from excel.

See the Attached.

Regards

Aviral Nag

Re: when I export values to excel from straight table I see additional rows

Sat, 01 Mar 2014 19:36:54 GMT

Nag,

I have used a VB script

Sub exportExel

set ExcelSheetApp = CreateObject("Excel.Application")

ExcelSheetApp.Visible = false

set ExcelWB= ExcelSheetApp.Workbooks.Add

ExcelWB.Sheets(1).name = "QV-Export"

set XLSheet = ExcelWB.Worksheets(1)

set QVData = ActiveDocument.GetSheetObject("CH230")

set XLSheet = ExcelWB.Worksheets(1)

QVData.CopyTableToClipboard true

XLSheet.Paste XLSheet.Range("A1")

ExcelWB.SaveAs "C:\Users\EANUMUS\Desktop\files\cc.xls", 51

ExcelWB.Close

ExcelSheetApp.Application.Quit

End Sub

Using a button – Run Macro Action, but when I click on the button nothing happens,

I have enabled all the system access and give system access to module script in change module security, but why isn’t the macro runs when I click on the button , I even treid calling at script level,

Is it due security issues, does my userid associated with qlikview needs to have some sort of permissions?

Appreciate your help

Re: Re: when I export values to excel from straight table I see additional rows

aveeeeeee7en — Sat, 01 Mar 2014 22:45:37 GMT

Hi Anusha

Your Macro is working fine on my System.

I think you're missing something.

You don' need any permissions to Run Macro on your Developer System.

You just to make following Settings in Macro Edit Module:

1) Requested Module Security - System Access

2) Current Local Security - Allow System Access

and these 2 settings you have done as you've said in your latest Post.

But if you have to Make Macros to Run on Server side than you have to make some settings:

See the Attachment for it - "Macro Settings on Server".

Read this post:

Change Macro security to allow System Access

See the Attachment for Macro Pivot Table Export.

If possible, kindly attach your Qvw.

Regards

Aviral Nag

Re: when I export values to excel from straight table I see additional rows

Sun, 02 Mar 2014 02:51:55 GMT

Please find this dummy qvw I have created to show what I am trying to do , correct me if I am missing something

Re: Re: when I export values to excel from straight table I see additional rows

aveeeeeee7en — Sun, 02 Mar 2014 06:39:24 GMT

I have seen your Qvw.

The Macro Name was written wrong. You have written export but it should be exportExel.

The starting line of macro starts with "Sub" and the name written after it, is the Macro Name

eg. Sub exportExel

__________

_________

End Sub

Here exportExel is the Macro Name.

See the changes you have to made in the images attached.

Below is the corrected Qvw. Just Run that on your system. It will work fine.

Regards

Aviral Nag

Re: Re: Re: when I export values to excel from straight table I see additional rows

aveeeeeee7en — Sun, 02 Mar 2014 16:08:53 GMT

Have you tried the Above Attachment.

Re: Re: Re: when I export values to excel from straight table I see additional rows

Sun, 02 Mar 2014 17:21:10 GMT

Hi Nag,

Right, I have been changing macro name quite a few times so totally lost track of it,

Thank you for your help

Re: Re: Re: when I export values to excel from straight table I see additional rows

Sun, 02 Mar 2014 17:29:03 GMT

Hi Nag,

On the other note, I am planning on creating email alerts when the server goes down , as I am new to the qlikview i am not aware on how do I go about it. Do you have any inputs on this?, I am using qlikview 11,

Appreciate your help

Re: when I export values to excel from straight table I see additional rows

aveeeeeee7en — Sun, 02 Mar 2014 17:59:39 GMT

Hi Anusha

Read below posts:

If your Application Reload Fails which you have set through QMC than its Mail Alert is possible, which can trigger to various users as per the email ids entered in the Alerts Box.

Re: Send an email if reloading QVW fails

How to send mail alert when server is down?

Hope that helps you.

Regards

Re: when I export values to excel from straight table I see additional rows

Sat, 03 May 2014 00:27:05 GMT