If else condition if we have data in combination of number,text

[grajmca_sgp123]

hi

I have a Period column with combination of integer and text.

I have a requirement to create a new filed,first it will check if it contains integer return same if not return "Others"

I have tried below logic,but didn't worked.

if(IsNum(Period)>0,Period,'Others') as Newfield

Period

03M

06M

01M

03M

06M

01D

F

RBWM

F

CMB

[tresesco]

Try:

if(IsNum(Period),Period,'Others') as Newfield                        // no '> 0'

[grajmca_sgp123]

tried but no luck,all are returning else part as "Others"

[stevedark · Accepted solution]

May be best to always treat it as a string and go for string functions only:

if(len(purgechar(Period, '0123456789')) = 0, Period, 'Others') as NewField,

You may also find that the num# function works

if(IsNull(num#(Period)), 'Others', Period) as NewField,

[Kushal_Chawda]

@grajmca_sgp123  try below

if(KeepChar(Data,'0123456789'),Data,'Others') as NewField

[grajmca_sgp123]

Thank you Steve,I just updated your logic and worked well.

=if(Len(KeepChar(sunf,'0123456789'))>0,sunf,'Others')

[stevedark]

Sorry, I assumed that as you were using IsNum in your original query you only wanted if the field only contained numeric. This is why IsNum will not have worked if the field was a mixed string.

Thanks for the update.