Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
schmimla21
Contributor
Contributor

Show differences of two Listboxes containing Strings

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!

Labels (3)
1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

6 Replies
Taoufiq_Zarra

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
schmimla21
Contributor
Contributor
Author

@Taoufiq_Zarra Here is an example: 

Screenshot of ListboxesScreenshot 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
Contributor
Contributor
Author

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

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
Contributor
Contributor
Author

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