Need help removing rows from a Notes, when there is no specific word to use to exclude

[lscanlon]

Hi:

I have a table the contains Notes. The notes expand multiple rows which are not the same in every case.  See example below...   I can't select on a specific Row #. I can't  select on specific words because you have no words in common on which to select for each ID.

The only thing I have to go on is that on the report I'm creating 

there is a line --------------------------------------------------------

There a bunch of NOTES

   NOTES

  NOTE

At the end of the notes there is the word CULTURE

QUESTION:  How do I remove the Notes between the lines and the word CULTURE without just saying  does not CONTAIN ([NOTE], "anything").

id  row   Note

A     1       Yes dkfdaldfj

A      2      ---------------------------------------------------

A      3       CULTURE

A      4       no dadalit

A      5       anything

B     1       could say anything

B     2       xxxxxx

B     3       ---------------------------------------------------

B     4       CULTURE

Thanks, Lenore Scanlon

[ScottE]

Hi @lscanlon, I'm not sure I completely follow what you're trying to do but if you line is a series of dashes, you could try doing a filter where NOTE contains dash "-"?

CONTAINS([NOTE],"-")

If the line is something other than a dash you could try to copy/paste from the cell viewer in the results grid to get the exact symbol. 

Hope that helps!

[lscanlon]

Hi ScottE:

I am not trying to remove the dashes.

I'm trying to remove the notes between the row of dashes and the row that says "CULTURE"

-------------------------------------------------------------

NOTES fashdfasif

NOTES odaidfjalsdk

NOTES any words in the english language.

CULTURE

I don't know how to remove those NOTE rows because there is nothing specific I can select on to remove. The notes could say anything.

Let me know if this is not clear.

Thanks, Lenore

[ScottE]

Hi @lscanlon 

The Filter outputs both the true and false results of your query, so by saying CONTAINS([NOTE],"-") you would get all the rows with dashes out the T anchor and all the other notes out the F anchor.

If you also want to capture the rows that say CULTURE you can add another contains function for that: 

CONTAINS([NOTE],"-")

OR

CONTAINS([NOTE],"Culture")

[lscanlon]

Hi ScottE:

See attached report.  Only the section circled in red would be what I want to exclude. So I don't think the False category from CONTAINS([NOTE], "-" would work because then the section below the CULTURE line would also apply. I don't want the lines above the ---------------------- or the lines below CULTURE to fall into the F because I don't want to get rid of those lines.

Thanks, Lenore

[AkimasaKajitani]

You can use the filter by judging the rows between "--------" and "CULTURE".

When you have to judge across row and row, you can use the Multi-Row Formula.

Multi-Row Formula Expression:

IF Contains([Row-1:Field1],"CULTURE") THEN 1
ELSEIF Contains([Field1],"-----") THEN 0
ELSE
[Row-1:Flag]
ENDIF

 - upper row of [Field1] Contain "CULTURE"  : flg = 1

 - current row of [Field1] Contain "-----" : flg = 0

 - others : flg = upper row of flg

Result : 

[lscanlon]

Thanks for this.... I am going in the right direction, but I think I have some bugs. 

Please see attached files and tell me where I am going wrong.

I would have expected these 2 like to be marked 1, and they got marked 0 and showed on the report. 

"R.R Wilber, M.D.. M.J. DeMeo, M.D...................................................."

"QUERIES: Sensitivities Requested? Y" to be set to 1 for FlagComments

Thanks, Lenore

[AkimasaKajitani]

Hi @lscanlon 

Looking at the screenshot, It seems the formula is correct.

However, the results are not as expected, so there is something wrong.

Would you able to provide a copy of the workflow for the relevant part only this logic without the data?

[lscanlon]

HI.  Thanks for looking into this.

See attachments. The section I highlighted in blue are assigned the value of 1-true and should not be.  

For the workflow, I did not include the select on only flag = 0 because I wanted to see the 0 and 1 together.

Thanks, Lenore

[AkimasaKajitani · Accepted solution]

@lscanlon Thank you for screenshot!

Now my workflow's output is different from your output.

Our unexpected result is occurred by the "CULTURE" in the middle of a row .
So that, I have to use Trim function and change to StartsWith function from Contains function.

IF (StartsWith(Trim([Row-1:DATA]),"CULTURE") or StartsWith(Trim([Row-1:DATA]),"URINE CULTURE")) THEN 1
ELSEIF StartsWith([DATA],"-----") THEN 0
ELSE
[Row-1:Flag]
ENDIF

Result:

But I don't know reason why the above of the "----" row is 1 flag.

Would you try to change the above point?