4 Replies Latest reply: Feb 23, 2015 4:00 PM by Dobri Danchev RSS

    Problem using Match against GetFieldSelections with mutiple selections


      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.


      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
      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
      LOAD distinct
      , DOB as Date
      , 'DOB' as EmployeeDateType
      RESIDENT Employees
      LOAD distinct
      , 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
      date($(vlocalMinDate) + IterNo() - 1) as Date
        $(vlocalMinDate) + Iterno() - 1 <= $(vlocalMaxDate)
      LOAD distinct
      RESIDENT EmployeeDates

      // Final Master Calendar
          , 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:
        + 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
                 =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!
        - Tom

        • Re: Problem using Match against GetFieldSelections with mutiple selections
          Gysbert Wassenaar

          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

              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

              Hi Gysbert,


              I have similar doubt.

              I m using expression like




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

              Data Management
              Data Timeliness
              Monitoring Visits
              Protocol Deviations


              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
                  Dobri Danchev

                  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:



                  LOAD * INLINE [

                  KRMO_MODULE_NAME, KRMO_MODULE_ID                    

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


                  Your expression will now be: