How to extract desired word from quoted sentence

[P_Kale]

Hi,

In my data I am having 1 column where text is in quoted form. Eg. is given below.

1) [{"choice":"agreed","screen":"personal_details"},{"choice":"agreed","screen":"policy_details"},{"choice":"agreed","screen":"terms_and_conditions1"}]  = score is "1"

2) [{"choice":"Not_Agreed","screen":"personal_details"},{"choice":"agreed","screen":"policy_details"},{"choice":"agreed","screen":"terms_and_conditions1"}]  = score is "0"

my ask is if in a sentence after choice "agreed" word is there 3 times then in new column I should get score as "1"  and if after choice "Not_Agreed" found any where then I should get score as "0"

Can you pl help how i can achieve this.

Thanks in advance

[Parthiban]

Try this,

if(WildMatch(fieldname,'*"agreed"*'),1,if(WildMatch(fieldname,'*"Not_Agreed"*'),0)).

[jpenuliar]

if you want to build it like a truth table :
you can make use of TextBetween and Subfield functions to break down the values.

you can then substitute "agreed" , "not_agreed" values to logic 1 and 0 to get your answer.

[barnabyd]

G'day @P_Kale,

This problem is much simpler than you think. You said that 3 x "agreed" should give a result of '1'. I noticed that there are only three choices in each string, so that means that if any of them is any other value then the result should be '0'. So my solution is:

if( index( lower( fieldname ), '"agreed"', 3 ) > 0, 1, 0 )

This searches for the beginning of the 3rd occurrence and only succeeds if that is found. Every other possibility comes back as 0.

I hope this helps.

Cheers, Barnaby.