topic How to extract desired word from quoted sentence in App Development

How to extract desired word from quoted sentence

P_Kale — Mon, 11 Dec 2023 10:05:55 GMT

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

Re: How to extract desired word from quoted sentence

Parthiban — Mon, 11 Dec 2023 10:19:53 GMT

Try this,

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

Re: How to extract desired word from quoted sentence

jpenuliar — Mon, 11 Dec 2023 12:34:26 GMT

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.

Re: How to extract desired word from quoted sentence

barnabyd — Mon, 11 Dec 2023 20:58:47 GMT

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.