Forum Discussion

darshthakkar's avatar
darshthakkar
Valued Contributor
3 years ago
Solved

Using list of values in Filter snap

Hi Team,

Is it possible to function a list of values in the filter snap?
Let’s assume, I want to filter IDs whose values are 100, 110, 300, etc., how can I put this in Filter snap without using || function?

I was planning to use $ID IN (100, 110, 300, etc.) but this one doesn’t seem to work so I have been using a conventional way as below:
$ID == "100" || $ID =="110" || $ID =="300"

Thanking in advance for your suggestions.

Regards,
Darsh

expression_language
pipeline
transform
  • siwadon's avatar
    siwadon
    3 years ago

    It depends. If your $ID are numbers, you can change the expression to

    [100, 110, 300].indexOf($ID) != -1

    I don’t understand your last question about Mapper. Since you mentioned using the Filter snap, this is for the Filter expression setting.

  • Abhishek_Soni37's avatar
    Abhishek_Soni37
    2 years ago

    I used the dummy data below to create a working solution.
    Input: 

     

     

    [
    {"txt":"abc"},
    {"txt":"efg"},
    {"txt":"c - abc"},
    {"txt":"c - xyz"}
]

     

     

     
    In the pipeline properties, I've defined a parameter filterValues that will contain the strings based on which you want to filter the records.
    Use the below expression in the filter snap:

     

     

    JSON.parse(_filterValues).map(x => $txt.contains(x)).filter(y => y == true).length == 1​

     

     

     
    The result based on the input above looks like this:
    The record that doesn't have the matching string defined in the filterValues parameter will be omitted.
     
    Hope this helps!
     
    Cheers😉,
    Abhishek
     
  • koryknick's avatar
    koryknick
    2 years ago

    darshthakkar - My solution is pretty similar to what Abhishek_Soni37 provided.  

    I would recommend that you not use JSON.parse() for every record.  It can be fairly taxing on your CPU depending on data volume.  If you want to remove the hardcoded reference to the array of values to search for, I recommend you look at Expression Libraries.

    Hope this helps!

     

12 Replies

  • koryknick's avatar
    koryknick
    Employee
    2 years ago

    darshthakkar - My solution is pretty similar to what Abhishek_Soni37 provided.  

    I would recommend that you not use JSON.parse() for every record.  It can be fairly taxing on your CPU depending on data volume.  If you want to remove the hardcoded reference to the array of values to search for, I recommend you look at Expression Libraries.

    Hope this helps!

     

    • darshthakkar's avatar
      darshthakkar
      Valued Contributor
      2 years ago

      koryknick - If I use 2 filter snaps would that be more effective than using ["abc", "xyz"].filter(x=> $txt.contains(x)).length >=1?

      • darshthakkar's avatar
        darshthakkar
        Valued Contributor
        2 years ago

        koryknick - CPU memory consumption as well as time taken to validate/execute the pipeline is pretty much the same with "abc", "xyz"].filter(x=> $txt.contains(x)).length >=1 as well as using 2 filter snaps.

        There have been instances where using 2 filter snaps is faster than using the expression detailed above however if we have to scale the solution and there is a need to use 10 filter snaps that needs to filters out strings then the solution provided by koryknick would be helpful.

        Tried using expression library too however everytime the string needs to be updated, expression library needs to be updated and putting them into a filter snap instead is easier and convenient approach.

         

        Thank you for your help koryknick and Abhishek_Soni37, closing this thread now.

    • darshthakkar's avatar
      darshthakkar
      Valued Contributor
      3 years ago

      Thank you @siwadon.
      I believe for the above suggestion, I’ll have to ensure that my numbers are strings and then use that function in a mapper, right?

      • siwadon's avatar
        siwadon
        Employee
        3 years ago

        It depends. If your $ID are numbers, you can change the expression to

        [100, 110, 300].indexOf($ID) != -1

        I don’t understand your last question about Mapper. Since you mentioned using the Filter snap, this is for the Filter expression setting.

  • Abhishek_Soni37's avatar
    Abhishek_Soni37
    Contributor
    2 years ago

    I used the dummy data below to create a working solution.
    Input: 

     

     

    [
    {"txt":"abc"},
    {"txt":"efg"},
    {"txt":"c - abc"},
    {"txt":"c - xyz"}
]

     

     

     
    In the pipeline properties, I've defined a parameter filterValues that will contain the strings based on which you want to filter the records.
    Use the below expression in the filter snap:

     

     

    JSON.parse(_filterValues).map(x => $txt.contains(x)).filter(y => y == true).length == 1​

     

     

     
    The result based on the input above looks like this:
    The record that doesn't have the matching string defined in the filterValues parameter will be omitted.
     
    Hope this helps!
     
    Cheers😉,
    Abhishek
     
  • darshthakkar's avatar
    darshthakkar
    Valued Contributor
    2 years ago

    siwadon - Hope you're doing well. Can we use multiple values in the string function ".contains()"?

    I have a column which has multiple values and I want to filter it out using a filter snap, let's say for instance the unique values in that column are - xyz, abc, pnr, stu, win, scp, c - xyz, c - abc, etc.

    I want to filter out all records where there is an existence of xyz and abc; how can I achieve this using a filter snap as .contains can't be used in conjunction with the function ".indexOf($ID)" . Can you please throw some light?

  • darshthakkar's avatar
    darshthakkar
    Valued Contributor
    2 years ago

    siwadon - We can easily use 2 filter snaps; one will have the string as $ID.contains (xyz) and the other filter snap as $ID.contains (abc) however if I have to filter multiple values where .contains() can be helpful, how can we do that effectively?

    Thank you.

     

    Regards,

    Darsh