Forum Discussion
vairable value for a calculated column based specific words found in text data
I have some of the ideas, but not all to create the calculated column I need.
I want to write a calculated column where the value varies depending what specific words it finds in the text.
There are the words we are looking for:
Big = 1
Black = 1
Cats = 1
Data and the value I would be expecting
I like big black cats = 3
I like big black dogs = 2
I like big ginger cats = 2
I like Black animals = 1
I like dogs = 0
I like cats = 1
I THINK that I need to write some scripting that, if written in just plain English would be like this:
Big_black_cats = IF(
(If Data contains = big then Big_black_cats = Big_black_cats +1)
(If Data contains = black then Big_black_cats = Big_black_cats +1)
(If Data contains = cat then Big_black_cats = Big_black_cats +1)
)
I know that’s NOT right, but can anyone get me started?
You can use the following to create a calculated column
CalcCats = var _black=IF ( ISBLANK ( SEARCH ( "Black", Table1[Data], 1, BLANK () ) ), 0,1) var _big=IF ( ISBLANK ( SEARCH ( "big", Table1[Data], 1, BLANK () ) ), 0,1) var _cats=IF ( ISBLANK ( SEARCH ( "cats", Table1[Data], 1, BLANK () ) ), 0,1) return _black+_big+_cats
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
3 Replies
You can use this calculated column
See attached file as well
Calculated Column = SUMX ( ADDCOLUMNS ( Words, "Number of Matches", IF ( SEARCH ( [Words], [Sentence], 1, 0 ) > 0, 1 ) ), [Number of Matches] )You can use the following to create a calculated column
CalcCats = var _black=IF ( ISBLANK ( SEARCH ( "Black", Table1[Data], 1, BLANK () ) ), 0,1) var _big=IF ( ISBLANK ( SEARCH ( "big", Table1[Data], 1, BLANK () ) ), 0,1) var _cats=IF ( ISBLANK ( SEARCH ( "cats", Table1[Data], 1, BLANK () ) ), 0,1) return _black+_big+_cats
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
Thanks so much for your help.
