We need to identify a 100% reliable way to detect the positions of the first and last characters of the text you want to extract.
What about the text: *XV*00660 in the second example. I can see delimitters of N1*PR* and ~ that appears to be consistent for both examples, but that then encloses the *XV*00660 along with the ANTHEM MIDWEST (KY, IN, OH, MO) that you wanted to extract.
Also, does this text start with N1*PR* 100% of the time?
Hi, thank you for your answer. The line always start with N1*PR. The problem is when a line comes with the name of the payer as in 'N1*PR*STATE OF FLORIDA MEDICAID~' and then another file ends as in the second example. I tried to use a suggestion of your I found using 'position" option and works whith the one example but not with all examples. For instance, Case(Initials="N1*PR";Middle( EDI;Position ( EDI ; "*" ; 2 ; 2 )+1 ;Position ( EDI ; "*" ; 2 ; 2 )-Position ( EDI ; "*" ; 2 ; 1 ));) Gets me the first three letters of what I want (I am not 100% sure how the'position' option works, but if I try to include a third * delimiter then the first example does not work since it only has two * delimiters.
The formula: Case(Initials="N1*PR";MiddleWords(EDI;3;WordCount ( EDI )-2);) works fine on the first example, but not on the second one
Thanks for your time, as always
Let's not get bogged down in calculation details until we can identify a workable pattern to the data being parsed.
Does this "rule" seem to work in all cases?
The delimitting character is either ~ or *--whichever appears first after the last character of the text that you want to extract and nether ~ nor * is ever part of the text that you want to extract.
If that works for your data, we can build a calculation that can work from that rule.
Hi, as I initialled said, sometimes the CSV file comes with only two * delimiters, some times it comes with more that two* delimiters, and always ends with a '~' . I was thinnking, is there a calculation that counts only delimiters, so to build a calculation based on the count?
Your example does not show that the text always ends in ~. In one example, it ends in ~ and in the other example, it ends in * with additional text: . *XV*00660 shown before the ~ appears. The fact that * appears more than once will not be a problem so long as:
a) the number of times the * appears before the one that might mark the end of the text is always the same in cases where we can't use ~ as the delimitter
b) the asterisk never appears as part of the text being extracted.
Is this the case?
The expression I have in mind is this:
Let ( [T = YourTable::YourTextFieldHere ;
Ptilde = Position ( T ; "~" ; 1 ; 1 ) ;
Pasterisk = Position ( T ; "*" ; 1 ; 3 )
If ( Pasterisk AND Pasterisk < Ptilde ; Middle ( T ; 7 ; Pasterisk - 7 ) ; Middle ( T ; 7 ; Ptilde - 7 ) )
) // Let
I've tested this expression in the DataViewer and it works for the two examples you've posted.
Phil, you are a magician; better, you know A LOT. We are blessed with you being here for us. It works like a charm, I just do not have enough knowledge of all the calculation formulas that can be used. I still do not understand each step on the formula, I have to digest it some more for future needs. Mil gracias.
Sorry to bother, how do I work the conditional, so ONLY the line with N1*PR gets calculated, I tried to include a Case contidional before the 'Let' command but there's an error.
Hi, disregard my last message. I replaced the last '//Let' on your formula by ';)and it worked. Thanks again.
is a comment. Any text to the right of // will be ignored in a FileMaker calculation. You can also enclose comments in /* comment goes here */ inside a calculation.
You can look up each of the functions I used in FileMaker help to learn more about them. If that is not enough to help you figure out how this works, feel free to ask additional questions here.