Show differences of two Listboxes containing Strings

[schmimla21]

Hi, 

I have created an app where I load data (strings) from an active directory and data from a specific database.  I am now stuck at a point where I have two listboxes as resulting data of interest. What I want to do now is to compare these two listboxes and get a third listbox which contains the difference. 

Even more precise: ListboxA shows the content of FieldA, ListboxB shows the content of FieldB. Now i want to have a third ListboxC which shows the elements, that are present in ListboxB (respectively FieldB) but not in ListboxA (respectively FieldA).  The two fields do not have a relation, so i can not use SQL to do this query in the load script.

I already created this third ListboxC and as underlying Field I selected a <formula>.  Does anyone have an idea how to define this formula?

Thank you very much in advance!

[Taoufiq_Zarra]

@schmimla21  can you share a sample and the expected output ?

[schmimla21]

@Taoufiq_Zarra Here is an example: 

Screenshot of Listboxes

In Reality Listbox A has about 300 Rows, Listbox B a few more. The underlying Fields contain mostly Surnames that match in LIstboxes A and B, but not all.  

[marcus_sommer]

How are the fields associated to each other and what's the aim behind your question? If they are "normal" associated you could use a tablebox or maybe table-chart to show both fields and you will see which didn't match or you used an expression.

But I assume it's not the case especially because you mentioned they contain names. To harmonize any unequally spelled names you may apply some logic in the script like capitalize(), trim(), keepchar() and so on but most common for such a task is the use of a mapping for it.

- Marcus

[schmimla21]

The fields are not associated. The fields mainly contain names, and most of them (but not all, because some rows in Field B do not represent names) are equal. All rows of Field A contain names, because this is the data extracted from the Active Directory. 

The aim is simply to have a list of these elements that are present in B, but not in A.  

[marcus_sommer · Accepted solution]

I don't want to say that there is no possibility to calculate it within the UI but probably it would be quite hard and very ugly - at least if the fields aren't properly associated. Whereby associated doesn't mean that the fields are directly linked else that they are connected through any unique ID.

Like above mentioned its much more easier to address such task within the script maybe with a mapping, like:

m1: mapping load Name, 0 from A;
m2: mapping load Name, 0 from B;

t: load Name as NameX from B where applymap('m1', Name, 1);
    concatenate(t) load Name as NameX from A where applymap('m1', Name, 1);

- Marcus

[schmimla21]

@marcus_sommer : Okay, I simply followed your advice and now the app is up and running. Thank you very much!