Forum Discussion

bobbybamber's avatar
bobbybamber
Frequent Visitor
8 years ago
Solved

Search a Field Within A Query List

Hi guys,   Apologies if this is obvious/has already been asked - I can't really work out what to even search for to find the answer so figured it'd be quicker to ask. Anyway - I have a dataset o...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi bobbybamber,

     

    Try this calculated column formula

     

    =FIRSTNONBLANK(FILTER(VALUES(Country[Country]),SEARCH(Country[Country],[Text],1,0)),1)

singhv2's avatar
singhv2
Helper I
8 years ago

Hi Ashish,

 

What if a string has more than 1 country. For me, I am trying to find our 3 or more than 3 values from a column in a string, and return.

 

rgds,


Vikrant Singh

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Could you show some data and also share the expected result.

  • singhv2's avatar
    singhv2
    Helper I
    8 years ago

    Ashish, 

     

    They are two seperate tables.

     

    Column/List A - Query1:

     

    Key Information.1

    ACCOUNTNBR:
    APPNAME:
    ANNVOLUME:
    ASTM:
    BILLTYPE:
    CICONTACT:
    CIPN:
    COMPONENT:
    CORE DIA:
    DELIVERY:
    DEPT:
    DEST:
    DIA:
    DIMENSIONS:
    DRWNUM:
    ECT:
    ENDUSE:
    EQUIPMENT:
    FEETYPE:
    FLANGE DIA:
    FORM:
    GRADE:
    HANDLE:
    HAZMAT:
    HEIGHT:
    HIMETAL:
    ITEM:
    JOB TITLE:
    LENGTH:
    LOCATION:
    MATERIAL:
    METERNBRS:
    MFGPN:
    MFRNAME:
    MILESTONE:
    MODEL:
    ORIGIN:
    OTHERINFO:
    PACKFACTOR:
    PACKTYPE:
    PAYTERM:
    PERFORATION:
    PRIMARY NAME:
    PRINTING:
    PROBLEM:
    PROCESS:
    QUOTE:
    RAM:
    REQUESTOR:
    SERIALNUM:
    SERVICEFREQ:
    SERVICELOC:
    SIZE:
    SOW:
    SPEC:
    SPECIAL FEATURES:
    SUBCAT:
    SUPCONTACT:
    TASK:
    TERM:
    THKS:
    TYPE:
    UNIT:
    UNITCOST:
    UOM:
    VERSION:
    WIDTH:

     

    Column/List B - From Query 2 (It's a long list, I am sharing just 2 for example: 

     

    PO Line Description
    CONVERGINT,TYPE:Embedded Technical Resource - Chris Cordes,SOW:System Administration,JOB TITLE:System Administration,BILLTYPE:Monthly,FEETYPE:Fixed Fee,CICONTACT:Mike Jackson,TERM:1 year,SERVICELOC:SP-ZV-02,OTHERINFO:6/16/17-6/16/18 - RBC1030048P
    CONSULTING (PROJECT MANAGER AND MASTER DATA ARCHITECT),TYPE:Corning Global Template Design,SOW:In Place ,FEETYPE:Hourly Rate,CICONTACT:Karin Stroh,TERM:Feb 12, 2018 - May 4, 2018

     


    List A Contains the Key information, out of which 3 or more should be available within each decription in List B.

     

    I just want to have the results, in a way that, query looks for each value of List A, in list B, and if present then return in in a new column.

     

    Our Results should look like below. (I've used some of the methods, however none of them are effective. Your Previous solution looks good, however, it's returning only one of the valus. I'll really appreciate your support.

    PO Line DescriptionKey Information Used (Result)
    CONVERGINT,TYPE:Embedded Technical Resource - Chris Cordes,SOW:System Administration,JOB TITLE:System Administration,BILLTYPE:Monthly,FEETYPE:Fixed Fee,CICONTACT:Mike Jackson,TERM:1 year,SERVICELOC:SP-ZV-02,OTHERINFO:6/16/17-6/16/18 - RBC1030048PType:, SOW:, JOB TITLE:, BILLTYPE:, FEETYPE:, CICONTACT:, TERM:, SERVICELOC:, OTHERINFO:
    CONSULTING (PROJECT MANAGER AND MASTER DATA ARCHITECT),TYPE:Corning Global Template Design,SOW:In Place ,FEETYPE:Hourly Rate,CICONTACT:Karin Stroh,TERM:Feb 12, 2018 - May 4, 2018Type:, SOW:, JOB TITLE:, FEETYPE:, CICONTACT:, TERM:
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      I am sorry but i do not know how to solve thsi problem.