topic Re: Macro with array variable in QlikView

Macro with array variable

Thu, 14 May 2015 20:58:43 GMT

Hi guys,

I am writing a macro to compare multiple values in a table (see below) , I want to be able to run a macro which compares every even and odd values and give me a result. For example,

COMPARE 12 and 5 (which is greater)

and then

COMPARE 26 and 45 (which is greater)

and do that 'n' number of times.

Header 1
12
5
26
45

This is what i wrote so far

Sub Risk

i = ActiveDocument.Variables("vi").GetContent.string /P.S (vi = 0)

dim A1(100)

i = i + 1

set mytext3 = ActiveDocument.GetSheetObject("CH27").GetCell(i,3)

ActiveDocument.Variables("A1").SetContent mytext3.text, true

Loop while i <= 3

End Sub

I want to store every result in an array. Please advise ? (Hope my question makes sense)

Re: Macro with array variable

petter — Thu, 14 May 2015 21:11:31 GMT

What kind of Array did you have in mind? A VBscript Array? Or do you refer to a Range in QlikView ... ?

Re: Macro with array variable

Thu, 14 May 2015 21:14:37 GMT

I am looking for an array which i can access in Qlikview to compare the values and show a trend chart. Is there a way to do it directly without writing a VB script?

Thanks for a response.

Best,

NIk

Re: Macro with array variable

petter — Thu, 14 May 2015 21:26:34 GMT

Yes - the thing you are trying to achieve in your VBScript Macro is very straight forward to do directly in a chart...

I have an example for you that you can see attached:

Re: Macro with array variable

Thu, 14 May 2015 21:35:13 GMT

This is certainly more straight forward. Just so i understand clearly, Mod(RowNo(), 2) is a modulo function will set row no to 1 and 2 respectively and Above [N'] is just taking the entire N' column and comparing every 2 rows.

I apologize if the question is a silly one and the answer maybe obvious. I am not familiar with QlikView syntax at all.

Thanks again for your time.

Best,

Nik

Re: Macro with array variable

petter — Thu, 14 May 2015 21:41:49 GMT

Don't apologize - your questions are not silly at all. I made the answer and solution in a hurry and didn't explain much. Even though it looks "easy" it takes some explaining especially if Above() is a new thing for you...

Above( [N'] ) alone just picks the value above the current row for the column named N' which has to be enclosed in the square brackets to be interpreted correctly by QlikView.

The Mod() function returns 0 or 1 in fact so I test for a 0 because that will be true on every even row.

Above() can take more parameters if you need to pick out more than one value from a column.

Re: Macro with array variable

Thu, 14 May 2015 21:51:03 GMT

Thanks for explaining. I have an access database connected to qlikview , and depending on the selection the user makes from different columns , the chart box gets populated after which i compare the values and insert a trend arrow.

What you have will fulfil what i am trying to accomplish give that the Row No() will only give me the rows of that chart box. I will give it a go and get back to you if i have more questions; if you dont mind ofcourse.

Thank you again,

Nik

Re: Macro with array variable

petter — Thu, 14 May 2015 21:54:11 GMT

Please do - and if you got your answer mark the question as answered. You can post a new question for additional help