How to find a matching value

[#1]

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,

R

[maleksafa]

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?

[#3]

Hi Malek,

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

[pokassov · Accepted solution]

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())

[#5]

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

[maleksafa]

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.

[#7]

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)

or

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

[#8]

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