15 Replies Latest reply: Jun 12, 2017 8:39 AM by Anjali Ahlawat

# string comparison of columns

Hi,

I have server list (let's say A,B,C ) in 3 different columns of different excels which are not at all linked.

I want to subtract the list of servers (B+C) from A and create one column D.

I am using the expression (something like),

if(match(A,B)=0,B) ,it is giving me values but when I am adding all these columns in one table then it is creating duplicates.

Please let me know what is wrong here.

Thanks.

• ###### Re: string comparison of columns

Where is C used here?

if(match(A,B)=0,B)

What exactly are you trying to do here? Can you provide 4-5 lines of data to show how the data looks like and what is the end result you are looking to achieve?

• ###### Re: string comparison of columns

I did not use column C - Sunny.

It was something that I was just trying with 2 columns,

I want the output to be like this :

But column A ,B and C are from different excels with no linking,hence there are multiple duplicates that are coming when I am trying to make straight table out of that.

Hope you understood my requirement.

• ###### Re: string comparison of columns

How can you add or subtract string values? Am I missing something here? How is x-(x+z) = y? Which sort of math is that?

• ###### Re: string comparison of columns

Probably something along the lines of: "Return me all server names in A that do not occur in either B or C"

• ###### Re: string comparison of columns

Ya, I am soo sry as I was in meetings side by side and i wanted to implement this logic asap , i could not write my requirement properly. I have the same requirement that you mentioned above.

• ###### Re: string comparison of columns

No problem. I got it. See the solution(s) below.

• ###### Re: string comparison of columns

TableToExclude:

FROM [Excel2.xlsx] ();

CONCATENATE (TableToExclude)

FROM [Excel3.xlsx] (); // Duplicates don't really matter much

ServerList:

NOCONCATENATE

LOAD A AS D // ???

FROM [Excel1.xlsx] ()

WHERE Not Exists(D, A);

DROP Table TableToExclude;

 Modified example to never generate duplicates, even if Excel1 contains them too...

• ###### Re: string comparison of columns

Hi Peter,

Thanks a lot.I got the output as expected.

In addition, I have to add columns B and C in the table as well but that I will figure out.

Thank u soooooooooooooooooooooo much

• ###### Re: string comparison of columns

Is this what you are looking for?

• ###### Re: string comparison of columns

Yes, I am looking for this output only.

• ###### Re: string comparison of columns

Refer the Attached qlikview document in the earlier reply then..

• ###### Re: string comparison of columns

Hey kamal,

I missed on one thing, I need the output in straight table not in list boxes.

and when I am adding all in one table ,it is creating duplicates that I was mentioning earlier.

Please look at the sample output table that I have added above.

Thanks.

• ###### Re: string comparison of columns

Here it is...

• ###### Re: string comparison of columns

And to answer your question, when you include multiple fields from non linked table in a table. It works like a cross join, hence you will see every item from List1 would have every corresponding item from List1.

• ###### Re: string comparison of columns

Also, you can easily remove the first dimension List (you might not require it). I just added it show all the servers in all the list.