Forum Discussion
How to avoid nested IF loops
assuming your timestamp is generated, then they match a specific pattern - one that you can use to split the column into junk and the actual error string.
Once you have the cleansed column, you can generate the error type in mutiple ways.
1. In the query editor, use a custom column and the if ... then ... else M syntax
2. As a calculated column
3. As look up table in the query editor defined as a join.
Some other thoughts: if it's possible, you should try to solve this at the source of the problem, when the errors gets generated, insert an error code/type so uniquely identify them. String searchs are never too reliable.
So, in trying to think this through, the only way I could see this working well is if you could come up with a specific number of characters that would make each specific type of error unique but also ensure that all of the same type of error are the same. You would then grab those using something like LEFT for n characters in a new column. Then, create another table that had all of these unique error type codes (exactly the same as returned by your LEFT n function) in them and perhaps a friendly name. Then, you could relate the two tables based on the LEFT n characters columns and you would have a much more efficient process.
If you have some lines that are very long and some lines that are not, such as:
Short error message 2015-12-29 10:34 AM
This is a big long error message that does not contain specific error text until right HERE 2015-12-29 10:34 AM
The problem is going to be that no single value of "n" will do. So, in that case, create multiple queries that filter out the results by specific lengths, so in query 1, it would only grab values that are 50 characters or less in LEN. You would then have a value for "n" of say 20. You have another query, query 2 that grabs everything that are more than 50 characters in length and this might have an "n" of say 75. Then, you merge the two (or more) queries together so that you end up with table that has all of you error codes trimmed correctly.