Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Comparing Text Strings

Hi,

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

Material BoughtMaterial Used
Brick; Stone; MortarBrick
Stone; RockRock; Wall Paper
Brick, PebblesStone

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!

5 Replies

Re: Comparing Text Strings

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

Employee
Employee

Re: Comparing Text Strings

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?

Not applicable

Re: Comparing Text Strings

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.

Not applicable

Re: Comparing Text Strings

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.

Not applicable

Re: Comparing Text Strings

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 BoughtMaterial Used
BrickStone; MortarBricks
StoneCobblestone

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:

BoughtUsed
Brick; MortarBrick
StoneSand; Mortar

Table_Bought:

Line_IDitem
1Brick
1Mortar
2Stone

Table_used:

Line_IDitem
1Brick
2Sand
2Mortar

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?

Community Browser