14 Replies Latest reply on Oct 25, 2013 12:10 PM by Jonathan Drummey

Matching String results

Hi,

I am trying to get a True or False based on Matching String.

Data:

Peg          Hole          Fit

Round      Square      WONT

Round      Round       WILL

Square     Round       WONT

Triangle    Triangle     WILL

My Current formula is:

IF STR([PEG]) = STR([Hole]) THEN 'WILL'

ELSE 'WONT'

END

It tells me i cant use the =

I guess i am doing this utterly wrong, Please help.

Ben

• 1. Re: Matching String results

I was able to use your data to do this:

IF [Hole]=[Peg] then "Will" else "Won't" End

See attached.  Are they not String fields to begin with?

• 2. Re: Matching String results

Hi Ben

Take a look at this package workbook with the solution

I created a data source with your data in a csv file, like this:

Peg,Hole

Round,Square

Round,Round

Square,Round

Triangle,Triangle

I create a calculated field with this formula:

```IIF(CONTAINS([Peg],[Hole]),'WILL','WONT')
```

I hoe this help.

Best,

ramon

• 3. Re: Matching String results

This should provide the same result in this case--this may perform faster on larger datasets, but I'm not certain.

CONTAINS could catch variations, like plural versions of strings, etc.

• 4. Re: Matching String results

Hi Ben,

Mathews' formula works properly in my workbook also. Yo don't need to apply the conversion function STR as fields Peg and Hole are strings

Best

Ramon

• 5. Re: Matching String results

ok, you are spot on, thank you very much, i think my issue may lay else where, so my real life Calculation is:

IF [Series Title]=[Sheet1 (WIPASSETS.xlsx)].[Series Title] THEN 'WIP'

ELSE 'DONE'

END

As you can see i am trying to compare the Text in the Field on one Workbook to the text in the filed on a second,

The first WB is shipped assets and we move them from the second WB (WIP) once they ship. There can be 20 odd assets to a series. so the idea was to use the above to show if the series still had any WIP titles or if all titles were shipped?

Does that make sense?

Your formula works fine if i remove the reference info for the second WB..

Thanks for the help

B

• 6. Re: Matching String results

Is it possible to post a sample workbook?

• 7. Re: Matching String results

Its two work books, The [sheet1 (WIPASSETS.xlsx).[Series Title])

is referring to the first Tab in the second workbook and the Series title column.

ill make two samples and see if that helps.

Thanks all,

B

• 8. Re: Matching String results

Sorry for not being clear, please make a sample Tableau packaged workbook.

• 9. Re: Matching String results

oh, haha.. sorry for being stupid!

ill get on it!

• 10. Re: Matching String results

Can you try the below calculation?

IF ATTR( [Series Title])=ATTR([Sheet1 (WIPASSETS.xlsx)].[Series Title] )THEN 'WIP'

ELSE 'DONE'

END

• 11. Re: Matching String results

This has worked, thank you very much. Can you explain what the ATTR does so i know for next time?

Thanks again,

Ben

• 12. Re: Matching String results

ATTR() is a Tableau-specific aggregation that is a computation of the form:

IF ISNULL([field]) THEN

Null

ELSEIF MAX([field]) == MIN([field]) THEN

[field]

ELSE

* // a special form of Null indicating there are multiple values for the given field for the current level of detail in the view

END

It's useful for sanity-checking when your data may change and you don't want to use just a MAX(), MIN(), or AVG() aggregation because they might return an incorrect value. However, it is slower than a MAX() or MIN() so I tend to use ATTR() in exploratory visualizations or ones that someone else not as familiar with the data might manipulate, and then in situations such as dashboards where performance is really key and I have enough control over the data I'll use a MIN() or MAX().

• 13. Re: Re: Matching String results

thanks Jonathan for the nice explanation.

Just adding one more point relevant to the issue.

When you did the data blending all secondary fields are aggregated. Dimensions are aggregated as Attributes (ATTR), which means that if there’s only one member it will show the member value but if there are multiple members it will show an asterisk.

ATTR Tableau description:

Returns the value of the given expression if it only has a single value for all rows in the group, otherwise it displays an asterisk (*) character. Null values are ignored. This aggregation is particularly useful when aggregating a dimension.

• 14. Re: Re: Matching String results

Good point on using dimensions from secondary sources!