topic Re: Problem using Match against GetFieldSelections with mutiple selections in QlikView

Problem using Match against GetFieldSelections with mutiple selections

Tue, 05 Mar 2013 22:32:25 GMT

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

Re: Problem using Match against GetFieldSelections with mutiple selections

Gysbert_Wassenaar — Wed, 06 Mar 2013 09:57:05 GMT

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

Re: Problem using Match against GetFieldSelections with mutiple selections

Wed, 06 Mar 2013 13:56:52 GMT

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.

Re: Problem using Match against GetFieldSelections with mutiple selections

Wed, 22 Jan 2014 11:11:02 GMT

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.?

Re: Problem using Match against GetFieldSelections with mutiple selections

ddanchev — Mon, 23 Feb 2015 20:57:41 GMT

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