Forum Discussion
Extracting text before 3 differents delimiters
Hi. I have column SKU from which i need to extract any elements before differents delimiters.
I know Extract is the approach but I dont realize how to declare the 3 differents delimiters in order to obtain the IDx column directly.
Any advice Will be welcome.
thanks , Sebastian
Sku | IDx |
44D000020062042 | 44 |
16921624301446 | 16 |
5920530001603 | 5 |
3910318800346 | 3 |
Give this a shot:
IDX = VAR TempId = LEFT(Sku[Sku],4) VAR Search_D = SEARCH("D",TempId,1,0)-1 VAR Search_91 = SEARCH("91",TempId,1,0)-1 VAR Search_92 = SEARCH("92",TempId,1,0)-1 VAR NewId = IF(Search_D>0,LEFT(TempId,Search_D), IF(Search_91>0,LEFT(TempId,Search_91), IF(Search_92>0,LEFT(TempId,Search_92)))) RETURN NewIdThis logic should provide you with what you need. Hope this helps! If so, please mark it as the solution!
6 Replies
jhartranft60Advocate IV
What are the 3 different delimiters? I think it may be easier for us to help you find a solution if we better understand the logic you're trying to build.
Hi
Sorry for the omission.
The delimiters are D, 91 and 92.
I need to get the characters before these delimiters which they always are present starting from the left of the string.
The IDx column shows the result expected.
Sku
IDx
delimiter applied
44D000020062042
44
D
16921624301446
16
92
5920530001603
5
92
3910318800346
3
91
- jhartranft60
One last question: Is there a max # of characters that the IDx can be? Is it never more than 2 characters before reaching a delimiter? Never more than 3? This is important because I believe I have a serviceable solution, but I need to make sure that a delimiter later in the sequence isn't inadvertently caught
For example: 55927465491 - In this example, I don't want the logic to to find the 91 at the end of this sku before it identifies the correct delimiter of 92. If there's a max number of characters, I can build that into the logic to prevent false-positives.
