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

    Problem using Match against GetFieldSelections with mutiple selections

      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

          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

               

              =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
                  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:

                   

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