topic Re: Type Mismatch error in QlikView

Type Mismatch error

Mon, 13 Jun 2016 15:53:27 GMT

I am using this code to change 1's to green and 0's to red. This works fine in Excel, but when I transfer the VBA code to qlikview, it no longer works, giving me the error "Type Mismatch:Range" at the third line. Any help would be appreciated.

Sub changeTextColor()

Dim v, clr, rng, x

Set rng = Range("Verifier Concat") //Problem at this line with "Type Mismatch"

Do While rng.Value <> ""

v = rng.Value

For x = 1 To Len(v)

Select Case Mid(v, x, 1)

Case "1": clr = RGB(0, 128, 0)

Case "0": clr = RGB(255, 0, 0)

End Select

rng.Characters(x, 1).Font.Color = clr

rng.Characters.Font.Bold = True

Set rng = rng.Offset(1, 0)

Loop

End Sub

Re: Type Mismatch error

swuehl — Mon, 13 Jun 2016 15:56:26 GMT

Well, Range is an Excel function, AFAIR.

What would you like to do with this code? I assume it should be possible to avoid the macro code completely.

Re: Type Mismatch error

Mon, 13 Jun 2016 16:10:38 GMT

Well I want to do similiar in Qlikview what I have done in Excel; turn a string of 1's and 0's such as 10010 into two different colours, red and green. It would be helpful if macros could be avoided altogether

Re: Type Mismatch error

swuehl — Mon, 13 Jun 2016 16:19:23 GMT

Qlik can't use per character text formatting, AFAIK, so what you are trying to do is not possible, I would assume.

Re: Type Mismatch error

Mon, 13 Jun 2016 16:30:32 GMT

Thanks for the help. Is there any way to specify a range in Qlikview like in Excel?

Re: Type Mismatch error

swuehl — Mon, 13 Jun 2016 16:43:11 GMT

Specifying a range to do what?

Re: Type Mismatch error

Mon, 13 Jun 2016 16:49:11 GMT

Specifying which range the code should act upon

Re: Type Mismatch error

swuehl — Mon, 13 Jun 2016 17:01:27 GMT

In QlikView, you would use either a conditional in your aggregations, like

Sum(If (Country = 'USA', Amount))

or a set expression

Sum({<Country = {'USA'}>} Amount)

to filter your records used in the aggregation.

Re: Type Mismatch error

Mon, 13 Jun 2016 17:28:02 GMT

Thanks again for the quick response.

Just to clarify, in Excel we set the range to column 'B', which included the values like 10001, 111, 000, etc. using the line:

Set rng = Range("B")

We would color code 1's with Green and 0's with Red, like in the image below. After looking up in Qlik Community, we realized that QV could not implement the individual character formatting like you said, so we pulled the same Excel field into QV as a column called 'Verifier Concat', and decided to go the macro route to try and implement similar functionality.

So in short, we just need to set the 'rng' variable to the 'Verifier Concat' field, instead of column 'B', on which we can apply the color coding for individual characters.

Re: Type Mismatch error

MarcoWedel — Mon, 13 Jun 2016 22:17:36 GMT

Hi,

depending on your requirements a solution might be a pivot table showing single characters of your string using individual colouring:

LOAD *,
    RecNo() as StringID,
    Mid(String,IterNo(),1) as Char,
    IterNo() as CharNo
Inline [
String
100010
011010
1101
100
00101
01010
01111
10011
10
01
111
]
While IterNo()<=Len(String);

hope this helps

regards

Marco