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
Solved! Go to Solution.
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!
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
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")
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
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 :
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
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 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?