Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
ToNumber(Substring([A],(Findstring([A],"IQ")+2),8) formula in alteryx
The formula using substring. With findstring it is finding the position of IQ in the string
and from IQ its counting the value (2,8) and giving output for that. i.e. 31085745150
how to achieve in the dataiku?
A - 85749439 PPPPI30071751 GIQ31085745 (This is the whole value in the string)
output - 31085745
Hi @prash_,
There are a bunch of different ways you can convert a string with the data "A - 85749439 PPPPI30071751 GIQ31085745" to one that finds an instance of "IQ" and gets "the next 8 characters after the appearance of IQ". Here is one example using a formula step in a Prepare recipe.
This splits your data at the instance of "IQ". It takes everything after the occurance of "IQ" and truncates to the next 8 characters ([0,8])
split(line, "IQ")[length(split(line, "IQ")) - 1][0,8]
If we apply the "split" setup, you can see we end up with an array that contains several values. Our last value is the one we want:
The index of the last value in our array if the length of our array - 1, so we use this to get the last value after the split. Then we get characters [0,8], giving us the expected final result:
There are other string functions that could also be used for this. Let me know if you have any questions about this! โ
Thanks,
Sarina
โ
@SarinaS solution is strong. If there is more variability in the shape of these strings I might look at regular expressions. These can be real brain benderโs to setup, but they can also be more robust to variability in input data. So although they have a steep learning curve, they can pay real dividends down the road.
Here is a bit of a description of regular expressions.
https://knowledge.dataiku.com/latest/ml-analytics/nlp/concept-regex.html
This knowledge base article that may more directly answer your question:
hope this might help a little bit.