topic Formula for if an entry starts with in Using Dataiku DSS
https://community.dataiku.com/t5/Using-Dataiku-DSS/Formula-for-if-an-entry-starts-with/m-p/8519#M4359
<P>Hello - I am looking to add a formula in a data recipe which will classify accounts based on their first 3 digits.</P><P>So for account numbers that start with '053' then I want the new column to cll these out as EQUITY (for example).</P><P>I can create formula based on accounts that == but I don't the syntax for start with - or contains??</P><P>Any ideas on how best to achieve this ?</P>Tue, 21 Jul 2020 14:29:32 GMTSeparateFiction2020-07-21T14:29:32ZFormula for if an entry starts with
https://community.dataiku.com/t5/Using-Dataiku-DSS/Formula-for-if-an-entry-starts-with/m-p/8519#M4359
<P>Hello - I am looking to add a formula in a data recipe which will classify accounts based on their first 3 digits.</P><P>So for account numbers that start with '053' then I want the new column to cll these out as EQUITY (for example).</P><P>I can create formula based on accounts that == but I don't the syntax for start with - or contains??</P><P>Any ideas on how best to achieve this ?</P>Tue, 21 Jul 2020 14:29:32 GMThttps://community.dataiku.com/t5/Using-Dataiku-DSS/Formula-for-if-an-entry-starts-with/m-p/8519#M4359SeparateFiction2020-07-21T14:29:32ZRe: Formula for if an entry starts with
https://community.dataiku.com/t5/Using-Dataiku-DSS/Formula-for-if-an-entry-starts-with/m-p/8523#M4361
<P>Even if I could create a new column which extracts the first 3 digits from the account column then I could use that, but I am also unsure how to do this. In Excel I could use a LEFT commmand</P>Tue, 21 Jul 2020 15:32:26 GMThttps://community.dataiku.com/t5/Using-Dataiku-DSS/Formula-for-if-an-entry-starts-with/m-p/8523#M4361SeparateFiction2020-07-21T15:32:26ZRe: Formula for if an entry starts with
https://community.dataiku.com/t5/Using-Dataiku-DSS/Formula-for-if-an-entry-starts-with/m-p/8524#M4362
<P>Hi,</P>
<P>Are you trying to process data using prepare recipe? if yes I could suggest using a "Replace" step. It allows you to define a regular expression patterns, so for the '053' -> 'EQUITY' the rule would look like this:</P>
<DIV id="tinyMceEditorAndrey_0" class="mceNonEditable lia-copypaste-placeholder"> </DIV>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Screenshot 2020-07-21 at 17.30.27.png" style="width: 400px;"><img src="https://community.dataiku.com/t5/image/serverpage/image-id/1500i677D9B141283E0AE/image-size/medium?v=1.0&px=400" role="button" title="Screenshot 2020-07-21 at 17.30.27.png" alt="Screenshot 2020-07-21 at 17.30.27.png" /></span></P>
<P>For the explanation of the demo expression that I used please refer to <A href="https://regex101.com/r/2mBStF/2" target="_blank" rel="noopener">https://regex101.com/r/2mBStF/2</A></P>
<P> </P>
<P>Regards</P>Tue, 21 Jul 2020 16:04:22 GMThttps://community.dataiku.com/t5/Using-Dataiku-DSS/Formula-for-if-an-entry-starts-with/m-p/8524#M4362Andrey2020-07-21T16:04:22ZRe: Formula for if an entry starts with
https://community.dataiku.com/t5/Using-Dataiku-DSS/Formula-for-if-an-entry-starts-with/m-p/8528#M4364
<P><LI-USER uid="3582"></LI-USER> </P><P>In your description, it appears that you are actually searching text strings, not integers. (I'm guessing this because you are referencing a number that begins with a "0".) You might try an expression like this in a Formula step of a prepare recipe. An expression like the below will yield a "true" or "false" boolean value. </P><BLOCKQUOTE><P>arrayContains(splitByLengths(id,3),"053")</P></BLOCKQUOTE><P>This can then be wrapped in an if statement to get the result you need. </P><BLOCKQUOTE><P>if(arrayContains(splitByLengths(id,3),"053"),"<SPAN>EQUITY </SPAN>","?")</P></BLOCKQUOTE><P>Here is a similar example that shows just getting true and false. That then could be used in an if() formula.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="Formula to Search for SubString.jpg" style="width: 999px;"><img src="https://community.dataiku.com/t5/image/serverpage/image-id/1501iD1BB1379B6B20F29/image-size/large?v=1.0&px=999" role="button" title="Formula to Search for SubString.jpg" alt="Formula to Search for SubString.jpg" /></span></P><P> </P><P> </P><P> </P>Tue, 21 Jul 2020 17:46:19 GMThttps://community.dataiku.com/t5/Using-Dataiku-DSS/Formula-for-if-an-entry-starts-with/m-p/8528#M4364tgb4172020-07-21T17:46:19ZRe: Formula for if an entry starts with
https://community.dataiku.com/t5/Using-Dataiku-DSS/Formula-for-if-an-entry-starts-with/m-p/8562#M4376
<P>Hi - thanks for coming back to me. I am intersted in this formula and it is working well for the most part but I am having an issue when the account numbers contain only numbers.</P><P>So an account number is 8 digits and these are a combination of numbers and letters - sometimes only numbers and this is where the formula fails.</P><P> </P><P>if(arraycontains(SplitByLengths(3,ACCOUNT),"040"),"Equity,""))</P><P>This will work when the account # in ACCOUNT contains letters (e.g. 04056F4D)<BR />But will faill when numbers only (e.g. 04045699)</P><P> </P><P>The data type for ACCOUNT is string so I thought that this shouldn't be an issue?</P><P> </P><P>Thanks again!</P>Wed, 22 Jul 2020 10:54:46 GMThttps://community.dataiku.com/t5/Using-Dataiku-DSS/Formula-for-if-an-entry-starts-with/m-p/8562#M4376SeparateFiction2020-07-22T10:54:46ZRe: Formula for if an entry starts with
https://community.dataiku.com/t5/Using-Dataiku-DSS/Formula-for-if-an-entry-starts-with/m-p/8589#M4380
<P>This formula looks to be missing a quotation mark. See the red <FONT color="#FF0000">"<FONT color="#000000"> I added below.</FONT></FONT></P><P><SPAN>if(arraycontains(SplitByLengths(3,ACCOUNT),"040"),"Equity<FONT color="#FF0000"><STRONG>"</STRONG></FONT>,""))</SPAN></P><P> </P>Thu, 23 Jul 2020 04:07:43 GMThttps://community.dataiku.com/t5/Using-Dataiku-DSS/Formula-for-if-an-entry-starts-with/m-p/8589#M4380tgb4172020-07-23T04:07:43ZRe: Formula for if an entry starts with
https://community.dataiku.com/t5/Using-Dataiku-DSS/Formula-for-if-an-entry-starts-with/m-p/8590#M4381
<P>What version of DSS are you using? I'm using the latest version V7.0.3.</P><P>This formula in your post looks to be missing a quotation mark. See the red <FONT color="#FF0000"><STRONG>"</STRONG><FONT color="#000000"> I added below.</FONT></FONT></P><P><FONT color="#FF0000"><FONT color="#000000">Also, the order of the parameters in the SplitByLengths() function seems to be backward I would assume you mean <STRONG>SplitByLengths(ACCOUNT,3)</STRONG> . This reflects the first 3 characters of the ACCOUNT column. </FONT></FONT></P><PRE><SPAN>if(arraycontains(<STRONG>SplitByLengths(ACCOUNT,3)</STRONG>,"040"),"Equity<FONT color="#FF0000"><STRONG>"</STRONG></FONT>,""))</SPAN></PRE><P>if none of that solves your problems you might wrap ACCOUNT in a <STRONG>strval()</STRONG> function.</P><PRE><SPAN>if(arraycontains(<STRONG>SplitByLengths(strval("ACCOUNT"),3)</STRONG>,"040"),"Equity<FONT color="#FF0000"><STRONG>"</STRONG></FONT>,""))</SPAN></PRE><P><SPAN>Hope that helps.</SPAN></P><P><SPAN>P.S. you can find short descriptions of all of the formula functions here.</SPAN></P><P><SPAN><A href="https://doc.dataiku.com/dss/7.0/advanced/formula.html" target="_blank">https://doc.dataiku.com/dss/7.0/advanced/formula.html</A></SPAN></P><P> </P>Thu, 23 Jul 2020 04:27:01 GMThttps://community.dataiku.com/t5/Using-Dataiku-DSS/Formula-for-if-an-entry-starts-with/m-p/8590#M4381tgb4172020-07-23T04:27:01ZRe: Formula for if an entry starts with
https://community.dataiku.com/t5/Using-Dataiku-DSS/Formula-for-if-an-entry-starts-with/m-p/8786#M4416
<P>I have this working peachy now - thanks for your help!</P>Thu, 30 Jul 2020 16:08:39 GMThttps://community.dataiku.com/t5/Using-Dataiku-DSS/Formula-for-if-an-entry-starts-with/m-p/8786#M4416SeparateFiction2020-07-30T16:08:39ZRe: Formula for if an entry starts with
https://community.dataiku.com/t5/Using-Dataiku-DSS/Formula-for-if-an-entry-starts-with/m-p/8792#M4419
<P>For others who might come to this post I invite you to go ahead and mark whichever posts helped you solve the issue as "Accept as Solution". Or consider adding anything else you have learned so the next person who has this problem can find your result.</P>Fri, 31 Jul 2020 00:55:55 GMThttps://community.dataiku.com/t5/Using-Dataiku-DSS/Formula-for-if-an-entry-starts-with/m-p/8792#M4419tgb4172020-07-31T00:55:55Z