topic Re: Comparing Text Strings in QlikView

Comparing Text Strings

Tue, 03 Feb 2015 17:20:35 GMT

Hi,

As part of our data checks, I have a table like below:

Material Bought	Material Used
Brick; Stone; Mortar	Brick
Stone; Rock	Rock; Wall Paper
Brick, Pebbles	Stone

I need to ensure that what is in column B is also in column A.

In the above example, row 1 is fine, but rows 2 and 3 are an issue. How can I compare these two fields for "likeness" without writing a bunch of if statements?

Thanks in advance!

Re: Comparing Text Strings

its_anandrjs — Tue, 03 Feb 2015 17:51:45 GMT

Hi,

But in second you have also Rock string do you find the first or second position of the string or it just presence of the strings in the field.

Regards

Anand

Re: Comparing Text Strings

Clever_Anjos — Tue, 03 Feb 2015 18:01:52 GMT

Do you want a "one-to-one" comparison?

Could you describe better your requirement, since "Brick" appears on first column, but what if have more values?

Re: Comparing Text Strings

Tue, 03 Feb 2015 18:02:47 GMT

I am looking for just the presence of the string. For row 2, column B should only include either Stone and/or Rock. Wall Paper would not be allowed.

Re: Comparing Text Strings

Tue, 03 Feb 2015 18:07:33 GMT

I would essentially like to write something like below:

IF([Material Used] like [Material Bought],'Okay','Not Okay')

and this would evaluate the two to see if the string from Material Used is contained within Material Bought. I want to avoid having to write if statements containing all the possible values.

Re: Comparing Text Strings

Wed, 18 Nov 2015 09:39:34 GMT

Hi Drew,

if I get you right, you have defined items, separated by a semicolon?
or is it undefined strings you want to compare like

Material Bought	Material Used
BrickStone; Mortar	Bricks
Stone	Cobblestone

That would make a different story, guess you would have to run loops on that. Ghastly.

As long as you can split up the items using a defined separator (";") as given in your sample, you may want to extract data (using the subfield command) into two tables, one for column 1 and one for the other, each with an id for the line and a field for the text.

The data would look like this:

Original table:

Bought	Used
Brick; Mortar	Brick
Stone	Sand; Mortar

Table_Bought:

Line_ID	item
1	Brick
1	Mortar
2	Stone

Table_used:

Line_ID	item
1	Brick
2	Sand
2	Mortar

Then, grouped per line_id, you count matches of both fields,
here resulting in a sum of 1 for line_id 1 and 0 for line_id 2.

Any line with >0 matches is what you are looking for.

Presto.

Would that help?