topic Re: string comparison of columns in QlikView

string comparison of columns

anjali0108 — Mon, 12 Jun 2017 11:08:09 GMT

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

sunny_talwar — Mon, 12 Jun 2017 11:10:36 GMT

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

anjali0108 — Mon, 12 Jun 2017 11:47:49 GMT

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

Peter_Cammaert — Mon, 12 Jun 2017 11:51:04 GMT

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

Re: string comparison of columns

kamal_sanguri — Mon, 12 Jun 2017 11:52:54 GMT

Is this what you are looking for?

Re: string comparison of columns

sunny_talwar — Mon, 12 Jun 2017 11:57:53 GMT

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

Peter_Cammaert — Mon, 12 Jun 2017 11:59:56 GMT

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

anjali0108 — Mon, 12 Jun 2017 12:00:43 GMT

Yes, I am looking for this output only.

Re: string comparison of columns

anjali0108 — Mon, 12 Jun 2017 12:02:40 GMT

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.

Re: string comparison of columns

kamal_sanguri — Mon, 12 Jun 2017 12:03:17 GMT

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

Re: string comparison of columns

Peter_Cammaert — Mon, 12 Jun 2017 12:06:44 GMT

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

Re: string comparison of columns

anjali0108 — Mon, 12 Jun 2017 12:08:07 GMT

Hey kamal,

Thanks for your time.

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

kamal_sanguri — Mon, 12 Jun 2017 12:25:44 GMT

Here it is...

Re: string comparison of columns

kamal_sanguri — Mon, 12 Jun 2017 12:33:15 GMT

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

kamal_sanguri — Mon, 12 Jun 2017 12:36:52 GMT

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.

Re: string comparison of columns

anjali0108 — Mon, 12 Jun 2017 12:39:48 GMT

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