2 Replies Latest reply: Jul 20, 2013 1:54 PM by Steve Zagzebski RSS

    Wildcard Match in Set Analysis

    Steve Zagzebski

      He have a field that is basically the result of a multi - selection box and has several values in it separated by a " ; " it looks like the following:

       

      Record1: Product A; Product D

      Record2: Product B;

      Record3 Product D;

      Record4: Product C; Product D; Product E

      Record5: Product A; Product D; Product E

       

      We also have a dimensional table with our Products in it:

      Product

      Product A

      Product B

      Product C

      Product D

      Product E

       

      I need to create an expression that will pull back any match from my multi selection field based on what I select from my Product Dimension.

       

      For example if I selected Product D it would find a match in Record 1, 2 and 4. If I select Products A and B then I would find a match in records 1, 2, and 5 .

       

      Thanks in advance for any help...Steve

        • Re: Wildcard Match in Set Analysis
          Gysbert Wassenaar

          What I think you should do is transform the data in the script:

           

          Records:

          load Record, subfield(ProductList,';') as Product

          from ...sourcetable...;

           

          Replace Record and ProductList with the real names of the fields from your source table.

           

          The load script above will split the Products list and create a record for each value. The result will be a table that looks like:

           

          RecordProduct
          Record 1Product A
          Record 1Product D
          Record 2Product B
          Record 3Product D
          Record 4Product C
          Record 4Product D
          Record 4Product E
          Record 5Product A
          Record 5Product D
          Record 5Product E

           

          This table will automatically be associated with the Products dimension table because both tables have a field named Product. You can now simply select a product in a listbox and the records will be filtered to show only those records with the selected product.