How to pull data if it begins with multiple different words?

Vaughn
Vaughn Altair Community Member
edited July 2020 in Community Q&A
Is it possible to filter a report if it begings with a certain word/cords. 

So for example if I have a column with a list of names. But I only want to find the names that BEGIN with JOHN and MATT. I have used instr before but I need only the first word. 

I have monacrh 15

------------------------------
Vaughn Mitchell
Data Analyst
------------------------------
Tagged:

Best Answer

  • Steve_Caiels
    Steve_Caiels
    Altair Employee
    edited July 2020 Answer ✓

    Hi Vaughn,

    In Classic, you can create a formula-based filter with multiple [Field = "Name*] components.

    image

    Give the filter a friendly name, make it active and build the expression:

    Name = "John*" .OR. Name = "Matt*"

    image


    You can link as many as you need by repeating the pattern.

    Name = "John*" .OR. Name = "Matt*" .OR. Name = "Sue*" .OR. Name = "Sophie*"

    Note that the Matt* will include 'Matt John' and 'Matthew Jones'. If you didn't want that, you could include the space in the expression "Name = "Matt *"

    In Data Prep Studio, we don't have an expression filter, but you can build a 'flag field' using the same technique, then filter on the flag field.

     

    image

     

    Regards,

    Steve.

    ------------------------------
    Steve Caiels
    Professional Services
    Altair
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 07-14-2020 05:26 PM
    From: Vaughn Mitchell
    Subject: How to pull data if it begins with multiple different words?

    Is it possible to filter a report if it begings with a certain word/cords.

    So for example if I have a column with a list of names. But I only want to find the names that BEGIN with JOHN and MATT. I have used instr before but I need only the first word.

    I have monacrh 15

    ------------------------------
    Vaughn Mitchell
    Data Analyst
    ------------------------------
    "

Answers

  • Steve_Caiels
    Steve_Caiels
    Altair Employee
    edited July 2020 Answer ✓

    Hi Vaughn,

    In Classic, you can create a formula-based filter with multiple [Field = "Name*] components.

    image

    Give the filter a friendly name, make it active and build the expression:

    Name = "John*" .OR. Name = "Matt*"

    image


    You can link as many as you need by repeating the pattern.

    Name = "John*" .OR. Name = "Matt*" .OR. Name = "Sue*" .OR. Name = "Sophie*"

    Note that the Matt* will include 'Matt John' and 'Matthew Jones'. If you didn't want that, you could include the space in the expression "Name = "Matt *"

    In Data Prep Studio, we don't have an expression filter, but you can build a 'flag field' using the same technique, then filter on the flag field.

     

    image

     

    Regards,

    Steve.

    ------------------------------
    Steve Caiels
    Professional Services
    Altair
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 07-14-2020 05:26 PM
    From: Vaughn Mitchell
    Subject: How to pull data if it begins with multiple different words?

    Is it possible to filter a report if it begings with a certain word/cords.

    So for example if I have a column with a list of names. But I only want to find the names that BEGIN with JOHN and MATT. I have used instr before but I need only the first word.

    I have monacrh 15

    ------------------------------
    Vaughn Mitchell
    Data Analyst
    ------------------------------
    "
  • Baba_Majekodunmi_703
    Baba_Majekodunmi_703
    Altair Employee
    edited December 2020

    Hi @Vaughn,

    I agree with Steve. You can also use RegexIsMatch()  and this works quite well.

    I used this for transactions when I was working at a Financial Institution. It was during the Govt shut down of 2018-2019, and we needed to find some specific transactions to identify which customers were furloughed, based on the deposit transactions (or lack thereof) from the Govt.

    can you tell us a little bit more about your use case here? I think RegexIsMatch would work well if it's similar to the use case I had.

    Best Regards,

    ------------------------------
    Baba Majekodunmi
    Solutions Consultant
    Altair Engineering Inc.
    Manassas VA
    2405932495
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 07-14-2020 05:26 PM
    From: Vaughn Mitchell
    Subject: How to pull data if it begins with multiple different words?

    Is it possible to filter a report if it begings with a certain word/cords.

    So for example if I have a column with a list of names. But I only want to find the names that BEGIN with JOHN and MATT. I have used instr before but I need only the first word.

    I have monacrh 15

    ------------------------------
    Vaughn Mitchell
    Data Analyst
    ------------------------------"

Categories