topic Re: How to find a matching value in QlikView

How to find a matching value

Tue, 14 Apr 2015 14:29:17 GMT

Hi,

I have a very simple question. How to find whether a column includes a specific value? I have writtem expressions and script in QlikView. In QlikView, there is no straightforward way to program simple expressions. Any assistance will be appreciated.

Rooms

Room1

Room2

Room3

Room4

Room2

Room3

I want to write a script or in an expression that verifies whether a column contains a specific value.

=if(Rooms='Room1',True,False)

=If(Rooms='Room2',1,0)

I am able to use the Count function to count the number of times "Room2" is included in Column "Rooms. But unable to match the column value. I tried Aggr, Sum functions. I have also written macros in Excel and familiar with other programning languages.

Thanks,

Re: How to find a matching value

maleksafa — Tue, 14 Apr 2015 14:32:26 GMT

so what exactly are you trying to accomplish? you have a table rooms and want to see if Room1,Room2 ... RoomN are available in the table?

Re: How to find a matching value

Tue, 14 Apr 2015 14:41:11 GMT

Hi Malek,

That is correct. The table "Rooms" is derived from another expression.I want to find out whether "Rooms" column includes "Room1".

Re: How to find a matching value

pokassov — Tue, 14 Apr 2015 14:42:08 GMT

Hi!

For script:

Let vCheck='Room2';

LOAD * Inline [

Rooms

Room1

Room2

Room3

Room4

Room2

Room3

];

for text object:

=if(FieldIndex('Rooms',vCheck)>0, True(),False())

Re: How to find a matching value

Tue, 14 Apr 2015 14:50:06 GMT

s1) Wildmatch conditional function:

Wildmatch(Rooms, '*1','*2','*3')

returns 1 if field value contain '1' as last char, 2 if contain '2' char, 3 if contain '3' char

s2) Mixmatch

Mixmatch(Rooms, 'room1','room2','room3')

same logic but value must match all character in the array definition, is NOT case sensitive

s3) Match

Match(Rooms, 'Room1','Room2','Room3')

same logic, value must match all character in the array definition and IS case sensitive

You can use these functions in front end and back end statements

Re: How to find a matching value

maleksafa — Tue, 14 Apr 2015 14:52:54 GMT

can you add a listbox with Rooms as dimension and click on 'Show Frequency'

or you could add a straight table with Rooms as dimension and in the expression use any count or aggregate function on any column available in tables Rooms, then go to presentation and un-check the option 'Suppress zero values', this way if the value if 0 it means that there are no data.

Re: How to find a matching value

Tue, 14 Apr 2015 15:00:47 GMT

In my opinion the most "performance friendly" and powerful solution is:

A) first create a custom "flag" field in load statement

LOAD

Rooms,

if(Wildmatch(Rooms,'*1'), 1,

if(Wildmatch(Rooms,'*2'), 2,

if(Wildmatch(Rooms,'*3'), 3

))) as RoomID

FROM [...]

B) after that use set analysis to simplify calculations

Count({<RoomID={1}>} Rooms)

Sum({<RoomID={1}>} MyFieldMetricValue)

Re: How to find a matching value

Tue, 14 Apr 2015 15:41:27 GMT

Hi,

I want to thank all of you providing the correct answer so quickly. I used the following expression in the "Text" Object properties, color "background" and it worked. This was based on response from "Cepren" sorry for spelling in English, above:

In script:

=vCheck='Room1';

In "Text" object "Properties -> General tab -> "Background" section -> "Color" field -> "Calculated" field, I added

= if(FieldIndex('Rooms',vCheck)>0, Color(2),Color(5))

The strange things was that I had previously tried using FieldIndex before posting this question to QlikView Community, but I typed Rooms without quotes, and QlikView did not evaluate the "If" logic correctly.

I also applied Match based on Carlo Belletti's suggestion and it worked also.

Thanks,

Raghu