Skip to Content

Group by particular values in multiple value field

I want a unique list of all IDs that contain value 1 from field X, regardless as to whether the ID also contains other values for the field, and a unique list of IDs that do not at all contain the value 1 from field X in the same report. Each ID can have multiple values from field X. I am not looking for a field per record or a summary that will tell me which group each ID fits into, but rather want to know how to get a group or way to get a list of IDs for each of the two conditions into one report.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Dec 11, 2017 at 08:13 PM

    Hi Alesha,

    1. Create a formula with this code:

    If {fieldx} LIKE '*value 1*' Then 'Group1' Else 'Group2' //you may name the groups anything

    2. Insert a group on this formula field.

    3. Place the ID field on the Details Section.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Hi,

      This does not work as if an ID has a 1 and another value, the ID will show in both group 1 and group 2. What I want is a list of IDs where if any of the values for the field for that ID are 1 make a group and a group where none of the values in that field for that ID are 1.

  • Dec 12, 2017 at 07:22 AM

    Hi Alesha,

    Could you please post an example with data and the expected groupings?

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Hello,

      Example of raw data:

      ID FieldX

      19 1

      19 5

      19 7

      18 1

      18 3

      17 3

      What I want are groupings or something that will give me lists in the same report that are:

      IDs that contain 1:

      19

      18

      IDs that do not contain 1:

      17

  • Dec 12, 2017 at 03:04 PM

    Hi Alisha,

    Modify the formula I posted above to:

    If {fieldx} = 1 Then 'IDs that contain 1' Else 'IDs that do not contain 1'

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • This results in the same thing. I do not want IDs to repeat as in the example provided. If an ID at all contains 1 I want them in group 1 and only group 1, so if an ID has 1 and 3 I only want that ID to show in Group 1. If an ID does not at all contain 1 I want them to show up once in group 2.

  • Dec 13, 2017 at 08:54 AM

    Ah, I missed some details. Here's what you need to do:

    1. Modify the formula above to:

    WhileReadingRecords;
    stringvar s; 
    if {FieldX} = 1 then
    (
        if instr(s,totext({ID},'#')) = 0 then //you don't need the string conversion using totext() if the ID field is string
        (
            s := s + totext({ID},'#');
        );
    );
    if instr(s,totext({ID},'#')) > 0 then 'Contains 1' else 'Contains 2'

    2. Insert a Group on this formula field.

    3. Insert a second group on the ID field.

    4. Suppress the Details Section.

    5. Suppress the Group Footer #2 section.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded