Sub Query Comparing two lists

Question

Hi Everyone,
I’m a bit stuck. Basically, I retrieve a list of people who have a flag set to true (list A). Then I get a list of active cases from CRM (List B). Then I want to check if the customer from List A exists in List B, and if they dont, set the flag to false. I can retrieve both lists, but I’m not sure how I then compare them?

j_angelevski · Answer

@chrisbodman,
You can do that with a mapper and filter() function,
$.filter(val =&gt; val.CustomerID != null)

This way, all rows where CustomerID is null will be filtered out.
You can do the same with a Filter Snap by writing $CustomerID != null

ptaylor · Answer

Have you tried using the Diff snap? You may need to use the JSON Splitter to split each list into separate documents that you can provide as input to the Diff snap.

ptaylor · Answer

Another possible approach is using the In-Memory Lookup snap. Again, this operates on lists of documents, so you might need to split your lists. You give list A to the first input, and list B to the second input. The In-Memory Lookup operates a bit like a SQL left outer join.  If a document from input view A matches a document from input view B, it will output a document that combines the two, otherwise it will just output the document from view A.

chrisbodman · Answer

Thank you for the suggestion. I went with the outer join, and it’s worked. Is it possible to skip a row in the mapper? I did an expression to say if Case = null, then set Customer to null, else give me the customer ID. But now I’ve got a list of nulls, with the customer id’s scattered through out. Is it possible to say if the incoming field doesnt contain a certain value, then dont map at all?

chrisbodman · Answer

All sorted thank you. I used the combination of a join, followed by a filter to weed out the records I didnt need!
Thank you!

Forum Discussion

Sub Query Comparing two lists

Recent Discussions

Pagination and nextCursor in header

Javascript to promote top level lists

Google Sheets Subscribe questions

Basic string transformations not working

Can we generate XML file in pretty print format using native snapLogic snaps?