topic Re: Use of selected value in macro in QlikView

Use of selected value in macro

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a macro to export some data from QlikView to Excel. In QlikView I have a multibox with a table column called "Cycle" which can take the following values:"Cycle 1","Cycle 2","Cycle 3" or none of these values. How can I change what my macro does, depending on whether I have selected a Cycle value or not, and the value of the said Cycle value?

Re: Use of selected value in macro

danielgarciamej — Mon, 29 Jan 2018 13:55:10 GMT

Hello Mihai

You can get the selected value of the field with the following function:

selectedVaue = ActiveDocument.Fields("Name Of Your Field").GetSelectedValues.Count

On the other hand, you can control what the macro does if you put different IF sentences inside your macro, like this:

If selectedVaue = 'Sample Value A of Cycle' Then

[Put the code here]

End If

If selectedVaue = 'Sample Value B of Cycle' Then

[Put the code here]

End If

I hope I've helped.

Best regards

Daniel

Re: Use of selected value in macro

zhadrakas — Mon, 29 Jan 2018 13:55:56 GMT

have a look at the QlikView api guide. There are examples for nearly everything you can do with macros:

API GUIDE

goto Automation exmaples tab and search for:

- .getselectedValues

- .Select

*mousover the selected value

Re: Use of selected value in macro

Anonymous — Mon, 29 Jan 2018 14:52:38 GMT

I'm using the personal Edition and I got an "License is embedded in this document is not valid" error.

Re: Use of selected value in macro

Anonymous — Mon, 29 Jan 2018 15:09:39 GMT

Could you help me with some macro lines?

Re: Use of selected value in macro

Anonymous — Mon, 29 Jan 2018 15:51:35 GMT

ActiveDocument.Fields("Name of my field").GetSelectedValues.Count only counts whether I have selected any value of the field "Cycle" or not. Which is partially useful in my future nested IFs in macro. But I need to get the value I have selected.

I've tried using only ActiveDocument.Fields("Name of my field").GetSelectedValues, to get the value that I have selected in the field, but I keep getting an "Unknown Runtime Error".More exactly the line with the error is:

XlDoc.Sheets(1).Range("Q1").Value = cycle_value

How can I use cycle_value in a Nested IFs? Am I using it wrong?

I'm looking for something like:
if cycle_value = "Cycle1" then

[Do something]

end if

if cycle_value = "Cycle2" then

[Do something else]

end if

if cycle_value = "Cycle 3" then

[Do something different]

end if

Re: Use of selected value in macro

danielgarciamej — Mon, 29 Jan 2018 17:24:49 GMT

I think that you have to develop a macro with the following structure:

0) Create a variable like:

vValueSelected = [Cycle Field]

1) Count the total values in you table column. Use:

totalValues = ActiveDocument.Fields("Cycle").GetSelectedValues.Count

2) Obtain all values and save into variable

Set allValues = ActiveDocument.Fields("Cycle").GetSelectedValues

3) A loop from i=0 to totalValues

4) (Inside the loop) Obtain the value of field Cycle of each iteration

valueCycleIteration = allValues.Item(i).Text

If valueCycleIteration = 'Value 1' Then

[Put some code]

End If

If valueCycleIteration = 'Value 2' Then

[Put some code]

End If

...

Re: Use of selected value in macro

tamilarasu — Mon, 29 Jan 2018 18:20:35 GMT

Hi Mihai,

Here is the possible scenario checks code. You can adapt this as per your need. Please let me know if you have any questions.

Sub Test

If ActiveDocument.Fields("Year").GetSelectedValues.Count = 0 Then

Msgbox "Nothing is selected"

Else

if ActiveDocument.Fields("Year").GetSelectedValues.Count >1 Then

Msgbox "Multiple Values Selected"

Else

SET Field = Activedocument.Fields("Year").GetSelectedValues

If Field(i).text ="Cycle1" Then
    [Do something]
Else
      If Field(i).text ="Cycle2" Then
        [Do something else]
      Else
        'If Field(i).text ="Cycle3" Then    ' You can uncomment this line if you want.
            [Do something Different]
        'End If
      End if
End If

End If

End If

End Sub

Re: Use of selected value in macro

Anonymous — Tue, 30 Jan 2018 07:03:46 GMT

Thanks, didn't knew that GetSelectedValues method returns an array.

Re: Use of selected value in macro

Anonymous — Tue, 30 Jan 2018 07:38:51 GMT

Here's how I'm using your scenario:

if cycle_flag(i).text = "Cycle1" then

Start_value = ActiveDocument.GetVariable("Cycle1_start")

End_value = ActiveDocument.GetVariable("Cycle1_stop")

else

if cycle_flag(i).text = "Cycle2" then

Start_value = ActiveDocument.GetVariable("Cycle2_start")

End_value = ActiveDocument.GetVariable("Cycle2_stop")

else

if cycle_flag(i).text = "Cycle3" then

Start_value = ActiveDocument.GetVariable("Cycle3_start")

End_value = ActiveDocument.GetVariable("Cycle3_stop")

end if

But I get a "Type mismatch: 'cycle_flag'" error

Re: Use of selected value in macro

tamilarasu — Tue, 30 Jan 2018 07:51:19 GMT

Here you go. Note that your Getvariable syntax is also not correct.

SET cycle_flag = Activedocument.Fields("Name Of Your Field").GetSelectedValues

if cycle_flag(i).text = "Cycle1" then

Start_value = ActiveDocument.GetVariable("Cycle1_start").GetContent.String

End_value = ActiveDocument.GetVariable("Cycle1_stop").GetContent.String

else

if cycle_flag(i).text = "Cycle2" then

Start_value = ActiveDocument.GetVariable("Cycle2_start").GetContent.String

End_value = ActiveDocument.GetVariable("Cycle2_stop").GetContent.String

else

if cycle_flag(i).text = "Cycle3" then

Start_value = ActiveDocument.GetVariable("Cycle3_start").GetContent.String

End_value = ActiveDocument.GetVariable("Cycle3_stop").GetContent.String

end if

Re: Use of selected value in macro

Anonymous — Tue, 30 Jan 2018 07:58:12 GMT

Now I'm getting an "Invalid procedure call or argument" error...

Here's the whole code:

set cycle_value = ActiveDocument.Fields("Cycle").GetSelectedValues

if cycle_value(i).text = "Cycle1" then

Start_value = ActiveDocument.GetVariable("Cycle1_start")

End_value = ActiveDocument.GetVariable("Cycle1_stop")

else

if cycle_value(i).text = "Cycle2" then

Start_value = ActiveDocument.GetVariable("Cycle2_start")

End_value = ActiveDocument.GetVariable("Cycle2_stop")

else

if cycle_value(i).text = "Cycle3" then

Start_value = ActiveDocument.GetVariable("Cycle3_start")

End_value = ActiveDocument.GetVariable("Cycle3_stop")

end if

Re: Use of selected value in macro

tamilarasu — Tue, 30 Jan 2018 08:00:16 GMT

Mihai,

I have already updated the above code.

set cycle_value = ActiveDocument.Fields("Cycle").GetSelectedValues

if cycle_value(i).text = "Cycle1" then

Start_value = ActiveDocument.GetVariable("Cycle1_start").GetContent.String

End_value = ActiveDocument.GetVariable("Cycle1_stop").GetContent.String

else

if cycle_value(i).text = "Cycle2" then

Start_value = ActiveDocument.GetVariable("Cycle2_start").GetContent.String

End_value = ActiveDocument.GetVariable("Cycle2_stop").GetContent.String

else

if cycle_value(i).text = "Cycle3" then

Start_value = ActiveDocument.GetVariable("Cycle3_start").GetContent.String

End_value = ActiveDocument.GetVariable("Cycle3_stop").GetContent.String

end if

Re: Use of selected value in macro

Anonymous — Tue, 30 Jan 2018 08:06:06 GMT

There seems to be an error at the : if cycle_value(i).text = "Cycle1" then

Re: Use of selected value in macro

tamilarasu — Tue, 30 Jan 2018 08:16:47 GMT

I think you have not selected any values in the Cycle field or you must be selecting multiple values.

Re: Use of selected value in macro

Anonymous — Tue, 30 Jan 2018 08:19:58 GMT

I have multiple values selected in a multi-box, but only one selected in the Cycle field. Could that be the issue?

Re: Use of selected value in macro

tamilarasu — Tue, 30 Jan 2018 08:28:57 GMT

Can you try this script and let me know whether you are getting any error message or not.

If ActiveDocument.Fields("Cycle").GetSelectedValues.Count = 0 Then

Msgbox "Nothing is selected"

Else

if ActiveDocument.Fields("Cycle").GetSelectedValues.Count >1 Then

Msgbox "Multiple Values Selected"

Else

set cycle_value = ActiveDocument.Fields("Cycle").GetSelectedValues

if cycle_value(i).text = "Cycle1" then

Start_value = ActiveDocument.GetVariable("Cycle1_start").GetContent.String

End_value = ActiveDocument.GetVariable("Cycle1_stop").GetContent.String

else

if cycle_value(i).text = "Cycle2" then

Start_value = ActiveDocument.GetVariable("Cycle2_start").GetContent.String

End_value = ActiveDocument.GetVariable("Cycle2_stop").GetContent.String

else

if cycle_value(i).text = "Cycle3" then

Start_value = ActiveDocument.GetVariable("Cycle3_start").GetContent.String

End_value = ActiveDocument.GetVariable("Cycle3_stop").GetContent.String

end if

End If

Re: Use of selected value in macro

Anonymous — Tue, 30 Jan 2018 08:39:39 GMT

I've run your script but I didn't got any message. At all.

Re: Use of selected value in macro

Anonymous — Tue, 30 Jan 2018 08:55:51 GMT

I've managed to solve the error by simply replacing cycle_value(i).text with cycle_value(0).text. I will mark your first answer as the correct. Thank you for all the help!

Re: Use of selected value in macro

tamilarasu — Tue, 30 Jan 2018 09:09:02 GMT

My bad. Glad that you found the mistake. Have a nice day!