Submit your innovative use case or inspiring success story to the 2023 Dataiku Frontrunner Awards! LET'S GO

unable to get the value.

prash_
Level 2
unable to get the value.

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

0 Kudos
2 Replies
SarinaS
Dataiker

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:

Screenshot 2023-05-26 at 3.58.46 PM.pngThe 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:

Screenshot 2023-05-26 at 3.59.26 PM.png

There are other string functions that could also be used for this. Let me know if you have any questions about this!  

Thanks,
Sarina

0 Kudos

@prash_ 

@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:

https://knowledge.dataiku.com/latest/data-preparation/prepare-recipe/tutorial-smart-pattern-builder.... 

hope this might help a little bit.  

--Tom
0 Kudos