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: 
anjali0108
Partner - Creator III
Partner - Creator III

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.

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

In your load script, you can try someting like this.

TableToExclude:

LOAD B as D

FROM [Excel2.xlsx] ();

CONCATENATE (TableToExclude)

LOAD C AS D

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;


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

View solution in original post

15 Replies
sunny_talwar

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?

anjali0108
Partner - Creator III
Partner - Creator III
Author

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 :

Capture1.PNG

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.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

In your load script, you can try someting like this.

TableToExclude:

LOAD B as D

FROM [Excel2.xlsx] ();

CONCATENATE (TableToExclude)

LOAD C AS D

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;


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

kamal_sanguri
Specialist
Specialist

Is this what you are looking for?

Capture.PNG

sunny_talwar

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?

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

anjali0108
Partner - Creator III
Partner - Creator III
Author

Yes, I am looking for this output only.

anjali0108
Partner - Creator III
Partner - Creator III
Author

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.

Anyways,my bad.And thanx for reverting.

kamal_sanguri
Specialist
Specialist

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