Sub Query Comparing two lists

[chrisbodman]

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?

[ptaylor]

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]

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]

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?

[j_angelevski]

@chrisbodman,
You can do that with a mapper and filter() function,

$.filter(val => 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