Search for a list of character strings in Text File or Json

Victor Morgan

I have two data-sets that I'm attempting to relate. Data Set 1 contains a list of strings in column A and the list of corresponding numbers for those strings in column B.  Data-Set 2 is a text file (from a json result). 

I'm unsuccessfully attempting make a list of 1B results if 1A exists in Data-set 2. 

I can use "query all records" to search for a single, static name (from 1A) exists in Data-Set 2 and it returns the corresponding data from 1B.

Do I need to put this in a loop to search for all items in 1A, can I just provide a list, or do I need to do something else entirely?

When I attempt to search for more than 1 item on the list I receive this error: "Exception: ParseError:"

Michael Udinski

Hey  can you provide some screenshots of your two data sets: 1 and 2? I need a little clarity of the two things you are trying to compare...

At first, it sounds like you can use the array function Find All Elements for dataset 1B 

Where
$this.whatever_field | IN | dataset 1A
=
true

Victor Morgan

I experimented with the $this.whatever_field | IN | dataset 1A but didn't have "IN" as an option.

This is dataset 1, with columns A and B.
[image.png]Dataset 2 is ideally a json file, single text block, or, if required, a list of text.
[image.png][image.png]
The goal is to go down all of the items in dataset 2, find the corresponding value in dataset 1 in the "Name" column, and return the corresponding "Number" value. 

In this example, the desired output would be: 
1
4
6
1

Victor Morgan

It seems like "Query all records" and the following filter would work the best (searching for the DataSet1.Name in the dataset2_textlist and then returning the corresponding DataSet1.Number), but I'm getting the following error.
[image.png][image.png]

Victor Morgan

I'm not allowed to create an array manually either. When typing in an array ["a", "g", "f"], it doens't save and reverts back to []. The input of [] runs without errors, but does not find matches.

[image.png]

Victor Morgan

I think that I got it. I needed to use "By Joins" to reference both data-sets/tables.

Michael Udinski

 IN is a filter within the Array function Find All Elements not an operator

Michael Udinski

 You can create an array manually if you use the import JSON feature on the drop down (Action > Import JSON)

Unknown

Hey  I'm having the same issue on my end as well


This is what my output looks like. In this example I want to filter for rows where "cancer_focus" contains the cancer_diagnosis_id "7". [image.png]When I tried to use the conditionals in my search (i.e. cancer_focus CONTAINS "7") it returns a blank value. I use the Safe Array filter to convert my value to an array in order to compare but it still returns blank.

Michael Udinski

 since your cancer focus field is an object array, you first need to bind the object array, which will then give you access to the individual fields within it to filter. Once you do so, I believe an equals operator would be used.