Problem using Match against GetFieldSelections with mutiple selections
Tom DeMerit Mar 5, 2013 5:32 PMHello,
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