Substring formula not workable

Solved!
TimLiao
Level 2
Substring formula not workable

Dears:

i use below formula 

substring('business_unit_code',6,3) or substring(business_unit_code,6,3)

want to pick up information from one string atttached.(I hope to get MCC or MSL)

but not workable. i have no idea what's wrong.

Thank you for the help.

raw data.jpg

0 Kudos
1 Solution
Manuel
Dataiker Alumni

Hi,

My example is working well:

- The index starts at 0 and the to is excluded, thus substring("http",2,4) = "tp"

- In your example, I believe it needs to be substring(col,5,8);

- See the detail explanation of substring in this page, https://doc.dataiku.com/dss/latest/formula/index.html#array-functions

On the engine:

- In the recipe, below the run button, you have an icon with 3 cogs;

- Clicking on it enables you to select between the available engines in your instance.

- See this page for an explanation on the different engines, https://doc.dataiku.com/dss/latest/preparation/engines.html

I hope this helps.

 

 

View solution in original post

0 Kudos
5 Replies
Manuel
Dataiker Alumni

Hi,

substring(column name, index, size) works for me (see attached image). Perhaps try substring(strval("column name"),index,size).

Also, which engine are you using? If not using it already, test the DSS engine as well.

I hope this helps.

Best regards

0 Kudos
TimLiao
Level 2
Author

Dear Sir:

noted with thanks. how to test the engine and please share me the training information for it.

thank you very much.

0 Kudos
TimLiao
Level 2
Author

BTW, from your screenshot, the result should be "ttps" instead of "tp". if my understanding is correct?

0 Kudos
Manuel
Dataiker Alumni

Hi,

My example is working well:

- The index starts at 0 and the to is excluded, thus substring("http",2,4) = "tp"

- In your example, I believe it needs to be substring(col,5,8);

- See the detail explanation of substring in this page, https://doc.dataiku.com/dss/latest/formula/index.html#array-functions

On the engine:

- In the recipe, below the run button, you have an icon with 3 cogs;

- Clicking on it enables you to select between the available engines in your instance.

- See this page for an explanation on the different engines, https://doc.dataiku.com/dss/latest/preparation/engines.html

I hope this helps.

 

 

0 Kudos
TimLiao
Level 2
Author

Dear๏ผš

Got it as the formula identification is different with excel's. 

Noted with thanks.

0 Kudos