Not bad, but how would I now replace the slash and unknown number of characters after each slash? Some temperatures are negative, some are zero, and some are positive, so the number of characters after each slash will be somewhere from 1 to 3, and all but the last instance of those would be followed by chr(13) once the above formula is applied.
So, ideally, I suppose I would take the entire formula above and
1) delete from slash to the character before chr(13). If there is a way to do this (i.e. delete multiple instances from a known character to the character before another known character), it would get rid of all except the last slash & temperature. Then that could be removed by 2) replacing the slash and all following characters (i.e. the final temperature).
I am familiar with INSTR, but that helps only for the slash itself. As I explained, I need to replace not only a varying number of instances of the slash (/), but also a varying number of characters following the slash--from the slash to the next comma, except for the last one, in which case there is no comma following. I really need some sort of iterative process that can find the first slash, delete from there to the next comma, replacing the comma with a line feed/carriage return, then, when there are no more of those, replace the final slash and any characters following with null.
That is, I need to change this:
5005219/-10, 5005220/0, 5005303/34
to this
5005219
5005220
5005303
?
In the first line, I need to remove the slash and three following characters. In the next one, it is the slash and one following character. In the last line it is the slash and two following characters. But the numbers (-10, 0, 34) are just examples; there may be other numbers there.
I did come up with a solution I made a function and view in my DB that produced this:
5005219,5005220,5005303 (i.e. without the slash & temperatures)
The number of digits before the slash will vary in every instance, as will the number of characters between the slash and the next comma and the number of entries (and therefore commas).
That is, as originally noted, I have to replace an unknown number of instances of an unknown number of characters at unknown positions. The constant is that I need to replace everything from each slash to each comma with a linefeed/carriage return, then when done with that, remove the last slash and everything following.
But, as noted, my solution was to come up with a function in my database that presents me with the data in a format that does not include the slash or the temperatures. Then it was easy to just replace each comma with a linefeed/carriage return.
Joined: 04 Mar 2008
Location: United Kingdom
Online Status: Offline
Posts: 70
Posted: 26 Oct 2016 at 11:28am
I want to add any more conditions to this formula. I copy the same statement with semicolon after the first
this parses. no errors. all works individually but putting the second command with different input string, to swap for appropriate string
this just gets ignored. maybe I need to use an evaluation time ..
before / after reading / writing?
just these two to test. There might be more. same input field, to swap to a better label. I tried each in different order. up/down. only one works, when syntax is matched. right('1234') to be abcd √ right('4321') to be dcba
each runs fine for each case alone. how implement in one formula, if I need 8?
nice that the rest of string before right('xxxx') stays the same, too. just replacing with new label.
can I make this formula work for several cases? need a different syntax? or put this outside of a formula somewhere? I could append the formula text but a bit embarrassing. just a personal test example
use a filter? just changing string contents, global conditions. could do in T-SQL, but can't import this data. Only Crystal can handle it
maybe why it fails: when one happens, running other command, can't see the digits cos it has changed to letters. No! I'm wrong. I want exactly the same command to function with the other digits. In the result, one was done, the other shows the same, not relabelled. should try alternative syntax.
thanks
Edited by mikebird - 26 Oct 2016 at 12:19pm
Life = luck (+/-) and effort (+/-). If you have no luck & make no effort, then you are dead
Joined: 04 Mar 2008
Location: United Kingdom
Online Status: Offline
Posts: 70
Posted: 27 Oct 2016 at 8:01am
I solved it √ easy
ignoring the Replace function
I started from scratch with if {report field} = 'xxxxx' then 'more detail'
else if {report field} = 'otherwise' the 'different appropriate detail'
else {report field}
putting this formula anywhere .. crosstab, list... makes perfect
I will add more cases to neaten the report
whenever new data is updated
Life = luck (+/-) and effort (+/-). If you have no luck & make no effort, then you are dead
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum