Problem using Match against GetFieldSelections with mutiple selections

[#1]

Hello,

New to QlikView; new to this Community.  Hi.

I have a problem where the Match() seems not to work properly for me when mutiple selections exist, but does work when only one selection is made.
I am working on a small, sample .qvw file, with 11.20 version of the Personal Edition.
We just last week received training on QlikView.

I am going to post below my load script, then a description of my Sheet objects, and my display problem.
I hope this is an acceptable forum procedure... I was not sure if a .qvw file should be attached, or not.

Thanks!

------------------------------------------------------------------
LOAD SCRIPT: start
------------------------------------------------------------------

//
// --(tab)--  Main
//

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

//
// --(tab)--  Employee
//

// Primary table of Employees
Employees:
LOAD * INLINE [
EmployeeID, EmployeeName, DOB, HireDate
1001, Mike Smith, 2/10/1966, 11/19/2012
1213, James Evans, 11/25/1914, 9/1/1999
1843, Sam Duncan, 10/10/1969, 9/1/2005
  ]
;

// Rollup of Employee Dates
EmployeeDates:
LOAD distinct
      EmployeeID
, DOB as Date
, 'DOB' as EmployeeDateType
RESIDENT Employees
;
Concatenate(EmployeeDates)
LOAD distinct
      EmployeeID
, HireDate as Date
, 'Hire' as EmployeeDateType
RESIDENT Employees
;

//
// --(tab)--  Calendar
//

LET vlocalMinDate = num(makedate(2013,1,1));
LET vlocalMaxDate = num(makedate(2013,12,31));

// Loads a Temp table with all dates for 2013 and also all Employee Dates
AllDates:
LOAD
date($(vlocalMinDate) + IterNo() - 1) as Date
  AUTOGENERATE
   1
WHILE
  $(vlocalMinDate) + Iterno() - 1 <= $(vlocalMaxDate)
;
Concatenate(AllDates)
LOAD distinct
Date
RESIDENT EmployeeDates
;

// Final Master Calendar
MasterCalendar:
LOAD
   *
    , Year(Date) as Year
    , Month(Date) as Month
    , Day(Date) as Day
    , Week(Date) as Week
    , Weekday(Date) as WeekDay
    , 'Q' & Ceil(Month(Date)/3) as Quarter
RESIDENT AllDates
;

DROP TABLE AllDates;

LET vlocalMinDate = ;
LET vlocalMaxDate = ;

------------------------------------------------------------------
LOAD SCRIPT: end
------------------------------------------------------------------

My Sheet has two List boxes:
  - Month (Jan thru Dec)
  - Day (1 thru 31)

I am showing a Straight Table with:
  + Dimensions:
           EmployeeName
           DOB
           HireDate
  + Expression
           "# of Dates" --> =Count(Date)

The sheet is supposed to show Employees who have either a DOB or HireDate for a selected Month.
We want the 'DOB' and 'HireDate' cell to go 'yellow' if the Month of a value matches a Month selection.
Thus, each of these two Dimensions has an attribute expression defiend for its Background Color:

  + DOB
            =If(Match(Month(DOB), GetFieldSelections(Month)) > 0, yellow())
  + HireDate
           =If(Match(Month(HireDate), GetFieldSelections(Month)) > 0, yellow())

It works great if only one Month is chosen -- e.g. Feb or Oct or Nov
But, when more than one month is selected -- e.g. Feb, Oct...
...then my attribute expressions fail.

To augment my diagnosis, I added the following expressions:
  + Expression
           =GetFieldSelections(Month)
           =Month(DOB)
           =Month(HireDate)
           =Match(Month(DOB), GetFieldSelections(Month))
           =Match(Month(HireDate), GetFieldSelections(Month))

(1) Why doesn't Match() seem to work when GetFieldSelections() returns more than one Month selection?

If I am doing something wrong, please let me know!
Thanks,
  - Tom

[Gysbert_Wassenaar · Accepted solution]

Getfieldselections returns a string like 'Feb,Oct,Nov'. That won't be an exact match with 'Feb' or 'Oct' or 'Nov'. If you were to match with a list of values, i.e. 'Feb','Oct,'Nov', instead of a string it would find a match. But you can use substringcount instead: =substringcount(GetFieldSelections(Month),Month(DOB))

[#3]

Thank you, Gysbert!

I now see the distinction between a list of values and a strike and how it affects Match() and substringcount().  The Help and Reference Manual, while thorough, do not always lend themselves to such distinctions.

I also see that reliance upon GetFieldSelections() can be tricky -- for example, selecting all Months except 'Dec' yields 'NOT Dec'.  So, one would have to be more comprehensive if one were to rely upon GetFieldSeclections() ultimately, and test for inverse strikes, too.

I am on my way.  Thanks again.

[#4]

Hi Gysbert,

I have similar doubt.

I m using expression like

=SubStringCount(concat(KRMO_MODULE_NAME,','),'Enrollment')

it is working fine.but values of field KRMO_MODULE_NAME are:

Compliance

Data Management

Data Timeliness

Enrollment

Enrollment/Discontinuation

Monitoring

Monitoring Visits

Protocol Deviations

Queries

Safety

SDV

So if I m matching the te string with 'Enrollment' , so there are two values containg this part of string.

So it is giving incorrect result.

So which function should i use to compare for the exact substring.?

[ddanchev]

Hello Sneha Topre,

try adding column with unique fields to you table. Than you execute against that unique ID column.

for example in your script you can add:

_KRMO_MODULE_TABLE:

LOAD * INLINE [

KRMO_MODULE_NAME, KRMO_MODULE_ID                    

Compliance, CO
Data Management, DM
Data Timeliness, DT
Enrollment, EN
Enrollment/Discontinuation,ED
Monitoring, MO
Monitoring Visits, MV
Protocol Deviations, PD
Queries, QU
Safety, SA
SDV, SDV

                         ];

Your expression will now be:

=SubStringCount(concat(KRMO_MODULE_ID,','),'EN')