Jump to content
Sign in to follow this  
MikeAir

IF-statement for searching text for word?

Recommended Posts

Hi,

 

I am doing some analysis of posts in a danish forum, and have arranged my data in a filemaker database.

Filemaker is way easier for creating dummy-variables and manipulating data, than SPSS(in which i am doing the statistical analysis).

 

My question is, whether it is possible to make an if-statement that returns 1 or 0, if a field contain a keyword in a text message.

 

Keyword: Apple

Fx: "I have long been looking at this apple product. But it seems to expensive to me" and the if-statement should then return "1"

 

I have been trying this:

if(post = "apple" ; 1 ; 0)

But i consequently get 0, even if the post has an instance of "apple"..

 

Is there a way around this?

 

Thanks,

Share this post


Link to post
Share on other sites
My question is, whether it is possible to make an if-statement that returns 1 or 0, if a field contain a keyword in a text message.

 

It is very easy to see if a field contains a string - for example by =

PatternCount ( YourField ; "apple" )

or, if you prefer to return a true-or-false (0 or 1) result =

GetAsBoolean ( PatternCount ( YourField ; "apple" ) )

However, it is not at all easy to test if the field contains the string as a word. For example, the above test would also return true if YourField contains:

 

"Dappled sunlight lay upon her straight brown hair."

  • Like 1

Share this post


Link to post
Share on other sites

Thank you! That was very helpfull. 

 

Fortunately the text is in danish, and i am going to create dummies for certain topics that are primarily english/not danish names(logic, ableton, reason, cubase, windows, mac etc.).

But I will be aware of the problem you pointed out, even though it might not be as bad as if it was pure english text.

 

:waytogo:

Share this post


Link to post
Share on other sites

I have a little question in addition to the other:

Is it possible to make a calculation in filemaker that counts the LIX-score?

http://en.wikipedia.org/wiki/LIX

(A/B) + (C*100/A) = LIX Score (readability measure)
I Currently have a count of the Amount of words( A ), amount of periods( B )..

Now i only need the amount of long words( C ) which are defined as words with over 6 characters..

Is that possible?

Share this post


Link to post
Share on other sites

Not easily. You need to loop over the words, one-by-one, and count those that are longer than 6 characters. Is there a maximum number of words the text can have?

Share this post


Link to post
Share on other sites

Not easily. You need to loop over the words, one-by-one, and count those that are longer than 6 characters. Is there a maximum number of words the text can have?

 

Ah okay..

 

The highest amount of words is: 5241

 

Maybe I need to resort to another program for this one..

 

 

EDIT: Found this on how to do it in Ms Access.

 

 

 

Assuming you're using Access 2000 or newer, you can use the Split function

to break each sentence into its individual words, then calculate the length

of each word. Something like the following untested aircode (it assumes that

the entire paragraph is stored in strFile):

Dim lngCountLongWords As Long

Dim lngCountWords As Long

Dim lngSentence As Long

Dim lngWords As Long

Dim strFile As String

Dim varSentences As Variant

Dim varWords As Variant

varSentences = Split(strFile, ".")

If IsNull(varSentences) = False Then

For lngSentence = LBound(varSentences) To UBound(varSentences)

' Extra spaces and the like may result in some elements in

' varSentences not actually being true sentences. For example,

' if there are 2 blanks in a row, you'll end up with an empty element

' in varSentences. You can vary the limit here if you like...

If Len(varSentences(lngSentence) > 0 Then

varWords = Split(varSentences(lngSentence), " ")

If IsNull(varWords) = False Then

For lngWord = LBound(varWords) To UBound(varWords)

If Len(varWords(lngWord)) > 0 Then

lngCountWords = lngCountWords + 1

' You'll likely want to vary this number too

If Len(varWords(lngWord)) > 9 Then

lngCountLongWords = lngCountLongWords + 1

End If

End If

Next lngWord

End If

End If

Next lngSentence

End If

--

Doug Steele, Microsoft Access MVP

 

Do you think it is possible to copy the logic of it to filemaker as well?

Share this post


Link to post
Share on other sites

Well, you need a recursive calculation here - so the program to resort to would be Filemaker Advanced, which allows you to construct a recursive custom function. In the absence of that, I'd suggest you script the process along the lines of:

Set Variable [ $text; Value:Table::Textfield ]
Loop
 Set Variable [ $i; Value:$i + 1 ]
 Exit Loop If [ $i > WordCount ( $text ) ]
 Set Variable [ $n; Value:$n + ( Length ( MiddleWords ( $text ; $i ; 1 ) ) > 6 ) ]
End Loop
...

At the end of the loop, the variable $n will hold the number of words in Textfield whose length exceeds 6 characters.

Share this post


Link to post
Share on other sites

@ comment

 

This is very cool, I hope you know that I am very thankfull for your help! This is quite important to my bachelor thesis, and after having googled a bit around, I really can't find any alternatives because the LIX count is for scandinavian languages(danish in this case).

 

The posts from the forum are located at: posts::post

I tried to replicate your code as a script:

Sk%C3%A6rmbillede%202015-03-03%20kl.%201

How can I implement it as a calculation in a field, so that the field will return the number?

I guessed there was a "run script" function in the calculation field, but it looks like this is not the case..

Edited by Lee Smith
Please do not quote the entire post

Share this post


Link to post
Share on other sites

How can I implement it as a calculation in a field, so that the field will return the number?..

As Comment said you will need a custom function which can be recursive (loops). The custom function can then be used in your calculation field. Unfortunately creating the custom function is only possible using Filemaker advanced. The good news is the education price is probably not too expensive.

Share this post


Link to post
Share on other sites

What is wrong with just writing a script? Maybe that is a skill you have not learned yet? You do know how to write scripts and execute them?

Share this post


Link to post
Share on other sites

As Comment said you will need a custom function which can be recursive (loops). The custom function can then be used in your calculation field. Unfortunately creating the custom function is only possible using Filemaker advanced. The good news is the education price is probably not too expensive.

Thanks,

 

I have a friend who has the developer/advanced version of filemaker 12. And I will try to ask him, if he can build that function for me..

 

I found this link: http://kallesamuelsson.blogspot.dk/2012/11/writing-recursive-custom-functions.html 

 

- Which gave me an idea of what is needed. And just to confirm.. which one is correct:

 

A ) You create the script with the code above, and then run a custom function, that links to the $n variable which was processed in the script?

B ) You create the custom function with the code above, and then just add the custom function to the new field with the word counts?

 

Im sorry, I might seem very stupid, but my FM skills are not that advanced yet(have only had FM for 1 year now)..

 

 


What is wrong with just writing a script? Maybe that is a skill you have not learned yet? You do know how to write scripts and execute them?

 

 

I would love do be able to do that. I guess the problem is, that i dont get the logic of the proposed method yet.

Looking at the script, it makes sense to me. But i do not understand, how you would get each field to execute it...

Share this post


Link to post
Share on other sites

In other words, you do not know what a script is and how completely different it is from a calculation. Time to learn about them, they are very important and fundamental to working with Filemaker.

Share this post


Link to post
Share on other sites
which one is correct:

 

A ) You create the script with the code above, and then run a custom function, that links to the $n variable which was processed in the script?

B ) You create the custom function with the code above, and then just add the custom function to the new field with the word counts?

 

 

Neither. A custom function is a custom function and a script is a script Two independent ways to get at the same result.

 

If I understand your situation correctly, you have a static collection of data which you intend to analyze. In such case, using a script would be a perfectly acceptable method. In the above example, you could have finished the script by:

Set Field [ Posts::CountLongWords ; $n ]

(where CountLongWords is a Number field), then use a calculation field to finish the job. Or you could have the script do the entire calculation, from top to bottom, all by itself:

Set Variable [ $text; Value:Posts::Post ]
Set Variable [ $wordCount; Value:WordCount ( $text ) ]
Loop
Set Variable [ $i; Value:$i + 1 ]
Exit Loop If [ $i > $wordCount ]
Set Variable [ $n; Value:$n + ( Length ( MiddleWords ( $text ; $i ; 1 ) ) > 6 ) ]
End Loop
Set Field [ Posts::LIX; $wordCount / PatternCount ( $text ; "." ) + 100 * $n /  $wordCount ]

(where again, LIX is a plain Number field).

 

 

To populate the LIX field across your entire data set of posts, you would have to run the script on each record. This, of course, can be automated by running another script.

  • Like 1

Share this post


Link to post
Share on other sites

MikeAir: do you in fact know what a script is and how to edit one?

 

Do you know what ScriptMaker is, where it is located in the FileMaker menus?

Do you know where the list of scripts is?

Do you know how to edit scripts (not calculations)?

Do all these screenshots look familiar?

post-62898-0-94085000-1425424797_thumb.p

post-62898-0-45993400-1425424806_thumb.p

post-62898-0-85782500-1425424814_thumb.p

Share this post


Link to post
Share on other sites

What is wrong with just writing a script? Maybe that is a skill you have not learned yet? You do know how to write scripts and execute them?

In other words, you do not know what a script is and how completely different it is from a calculation. Time to learn about them, they are very important and fundamental to working with Filemaker.

Bruce, Not everyone has your excellent FileMaker skills, knowledge, and ability. However, everyone can be polite when posting to our forum. Your 2 posts come across a little tart to me.

Please remember that we attempt to help all skill levels, even those who are beginners, please keep that in mind when helping other members.

TIA for your cooperation.

Lee

  • Like 1

Share this post


Link to post
Share on other sites

Neither. A custom function is a custom function and a script is a script Two independent ways to get at the same result.

 

If I understand your situation correctly, you have a static collection of data which you intend to analyze. In such case, using a script would be a perfectly acceptable method. In the above example, you could have finished the script by:

Set Field [ Posts::CountLongWords ; $n ]

.........

 

To populate the LIX field across your entire data set of posts, you would have to run the script on each record. This, of course, can be automated by running another script.

 

Thank you so much! the first part of your script didnt make that much sense to me, cause i couldnt figure out how it would 'print' the information to each record.

 

Copied your code, and it works!!  :)

 

Is it correctly observed that you do the LIX calculation in the last Set Field line:    (A/B) + (C*100/A) = LIX

 

I tried running the script manually (cmd + 1) on 35 records, and 4 of them return a '?'.. This is one of them:

 

hey, er der nogen der har en idé til hvordan man kan fjerne lyden af at vokalisten er 'for tæt på' mikrofonen under optagelsen? Mon ikke det kan gøres vha af lidt EQ? Emil

 

Do you know why it does that?

 

 

MikeAir: do you in fact know what a script is and how to edit one?

 

Do you know what ScriptMaker is, where it is located in the FileMaker menus?

Do you know where the list of scripts is?

Do you know how to edit scripts (not calculations)?

Do all these screenshots look familiar?

 

Yes, i do. However, as stated before.. I'm not that good, so I am learning new things every day.

 

Next step for me is to figure out how to automate the LIX-script i just recieved from comment.

Share this post


Link to post
Share on other sites

Thanks for helping us understand where you are with script editing and operation, and the particular area where your script encountered problems.

Earlier replies seemed a bit ambiguous and it sounded kinda like you were trying to get results entirely with a field calculation.

 

Attached is an example file that uses the script.

In one layout, it is actuated only with a button (or from the script menu)

In the second layout, a script trigger is used, and the act of editing the field triggers the script.

Posts.fmp12.zip

post-62898-0-10027000-1425427409_thumb.p

  • Like 1

Share this post


Link to post
Share on other sites

Thanks for helping us understand where you are with script editing and operation, and the particular area where your script encountered problems.

Earlier replies seemed a bit ambiguous and it sounded kinda like you were trying to get results entirely with a field calculation.

 

Sorry for not making that clear enough. Communication is not always that easy in a foreign language...

Share this post


Link to post
Share on other sites
I tried running the script manually (cmd + 1) on 35 records, and 4 of them return a '?'.. This is one of them:

 

Do you know why it does that?

 

I do, actually. It's the result of dividing by zero.  My (too narrow) definition of B is "the number of periods in text" - and your text has none. Obviously, my definition needs to be expanded, but I am not entirely sure how. The linked article says "period, colon or capital first letter". That's a rather problematic description, because surely you don't want to count both periods and the capital first letters that follow a period? Perhaps you could standardize on a few punctuation marks that should be followed by a capital letter, say make B =

PatternCount ( $text ; "." ) + PatternCount ( $text ; ":" ) + PatternCount ( $text ; "?" ) + PatternCount ( $text ; "!" )

and perhaps a few others that have slipped my mind.

Share this post


Link to post
Share on other sites

I do, actually. It's the result of dividing by zero.  My (too narrow) definition of B is "the number of periods in text" - and your text has none. Obviously, my definition needs to be expanded, but I am not entirely sure how. The linked article says "period, colon or capital first letter". That's a rather problematic description, because surely you don't want to count both periods and the capital first letters that follow a period? Perhaps you could standardize on a few punctuation marks that should be followed by a capital letter, say make B =

...

Well, thank you very much! I really appreciate your help.

These LIX counts, and the ability to spot keywords in topics is some important aspects of answering my RQ: Which factors influence the amount of answers the users get to their questions in the danish online-forum for sound technicians.

 

Today i've learned a lot about filemakers text mining / data manipulation capabilities(as well as the ability of scripts). And filemaker actually does a way better job than SPSS at this particular job.

 

So I guess I will continue to do my data manipulation/preparation in FM before doing the in-depth analysis in SPSS.

 

Thanks again  :waytogo:

Share this post


Link to post
Share on other sites
Which factors influence the amount of answers the users get to their questions in the danish online-forum for sound technicians.
 
Oh, is that what you're investigating? My guess would be that you will find very little to no correlation between the lexical quality of a question and the amount of answers it attracts.

Share this post


Link to post
Share on other sites

By all means, post your results. This is a learning forum and others can benefit from your question and its results.

  • Like 1

Share this post


Link to post
Share on other sites

Well, I can post the results in here if you're interested? 

 

I would be interested to learn if your results are different (in terms of yes or no - I have plenty of reading material, thank you).

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.