Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

anjali0108
Contributor II

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

Re: string comparison of columns

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...

15 Replies
MVP
MVP

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?

anjali0108
Contributor II

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 :

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.

Re: string comparison of columns

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

Re: string comparison of columns

Is this what you are looking for?

Capture.PNG

MVP
MVP

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"

anjali0108
Contributor II

Re: string comparison of columns

Yes, I am looking for this output only.

anjali0108
Contributor II

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.

Anyways,my bad.And thanx for reverting.

kamal_sanguri
Valued Contributor

Re: string comparison of columns

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

Community Browser