Qlikview Export to Excel: Only specific columns of a straight table

[davidl60]

Hello Qlik community,

I'm looking for a way to export to excel some columns of a straight table. I've got a table with 60 columns, number of rows varies depending on the filters set by the users. I've got a button to export the full table (60 col and all rows) to an excel file with formatting and so on...  which works well. I need to incorporate after the first export, another one (use as an archive) but only with 5 columns of the 60. These 5 columns are always the same (columns 6, 7, 48, 10 and 51), they are not a user selection. They are dimensions and expressions. I didn't find the way to do this.

I already found a lot of solutions to my problems on this forum, so thank you all for knowledge sharing!

Regards

David

[m_woolf]

Make a second chart with only the 5 restricted columns. 
Hide the chart.
Use a button to export the new chart.

[davidl60]

Hello,

That's what I've done but I expected a solution avoiding to create another table.

If there is no other solution, I'll do it that way.

Thank you

[marcus_sommer]

An alternatively to the use of multiple objects would be to apply (per variable-value) a show-condition to all columns respectively certain ones and then an inputbox or a button could be used to change the variable-value.

But is this the better approach? Not necessary - personally I use specialized objects for prints/exports (usually in hidden sheets) far more often then the show-conditions because it's more flexible and copy & paste of the objects and then to apply the needed adjustments is often very easy and faster as applying the show-conditions.

- Marcus

[davidl60]

Hi Marcus,

I didn't want to create another table so the workaround I use is:

1. Create a variable vHide and set it to 0
2. For all columns I want to hide, I apply a show-condition vHide=0
3. Button1 exports all columns in an Excel file: fine!
   • Here, I've got a small issue as Excel window is not maximized. I don't know how to maximize it.
4. Button2 changes vHide to 1, export the 5 visible columns, move back vHide to 0

I use the same macro for both buttons like this:

sub Export2XL()

on error resume next

dim vHideShow
vHideShow = ActiveDocument.Variables("vHide").GetContent.String
Msgbox vHideShow
if vHideShow = 0 Then

'Export all columns

Else

'Export only the 5 visible columns

End if

vHideShow is 0 when I click on the first button

vHideShow is 0 when I click on the second button despite : Set Variable / vHide = 1

I did a test via a text box, changed vHide to 1, columns are hidden but when I press button 2, I can see vHide changes to 0!! I don't understand what is going wrong?

If I don't get it sorted, I'll create a second table.

Thank you for your advices,

David

[marcus_sommer · Accepted solution]

The defined actions within a button aren't executed sequentially else in parallel and therefore you couldn't chain tasks which are depending on eachother (although it often worked you won't get it really stable for each scenario).

But it's not necessary in your case because you could change the variable-value within the macro, for example with:

ActiveDocument.Variables("vHide").SetContent 1, true

- Marcus

[davidl60]

Hi Marcus,

Thank you for the trick! It works perfectly.

However, now I've got another question:

I've got a field 'Category' that may be used as a filter.

For the name of the Excel file, I want to test if 'Category' as a selected value.

I've tried this but it doesn't work.

dim vCat
vCat = ActiveDocument.Fields("Category").GetSelectedValues

msgbox vCat ' shows nothing even though a value is selected in Category !

if vCat = "bicycle" then

... saveas  bicycle.xlsx

ifelse vCat = 'car' then

... saveas car.xlsx

else

... saveas allcategories.xlsx

end if

Thank you

David

[marcus_sommer]

The assignment of the variable and their calling is wrong because the selection isn't a single value (even if only one value is selected) else it's an object and needs to be accessed with a set statement. This means it should look like:

set vCat = ActiveDocument.Fields("Category").GetSelectedValues

msgbox vCat.item(0)

If there are several values you will need to loop through the object.

- Marcus

[davidl60]

Hello Marcus,

Thank you for your reply but I can't get it running properly.

Finally, I used a variable vCat =GetFieldSelections(Category)

In the macro, I read the value of this variable (ActiveDocument.Variables("vCat").GetContent.String),

and I save the excel file according to the variable value.

Thank you for your help. Much appreciated.

David