10 Replies Latest reply on Apr 4, 2013 2:15 PM by yomango

    Substracting information from a CSV line

    yomango

      Title

      Substracting information from a CSV line

      Post

           Hello, there. On the same line in two CSV files I have the following information:

           N1*PR*STATE OF FLORIDA MEDICAID~

           N1*PR*ANTHEM MIDWEST (KY, IN, OH, MO)*XV*00660~

           what I want to extract is 'STATE OF FLORIDA MEDICAID' from one file, and 'ANTHEM MIDWEST (KY, IN, OH, MO)' from the other file using the same formula. I've tried many approaches but when a calculation works on a file, it fails on the other. Can anyone give me a guidance on how to approach the calculation? Thanks

        • 1. Re: Substracting information from a CSV line
          philmodjunk

               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?

          • 2. Re: Substracting information from a CSV line
            yomango

                 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

            • 3. Re: Substracting information from a CSV line
              philmodjunk

                   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.

              • 4. Re: Substracting information from a CSV line
                yomango

                     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?

                      

                Thanks,

                • 5. Re: Substracting information from a CSV line
                  philmodjunk

                       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.

                  • 6. Re: Substracting information from a CSV line
                    yomango

                         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.

                    • 7. Re: Substracting information from a CSV line
                      yomango

                           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.

                      • 8. Re: Substracting information from a CSV line
                        yomango

                             Hi, disregard my last message. I replaced the last '//Let' on your formula by ';)and it worked. Thanks again.

                        • 9. Re: Substracting information from a CSV line
                          philmodjunk

                               // Let

                               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.

                          • 10. Re: Substracting information from a CSV line
                            yomango

                                 Thank you