Nested IF issue

[ivandrago]

Hi,

I have the attached Document, I am trying to do an Nested IF but it is not returning the correct results for the field NewTableName.

What I want my new field ( NewTableName.) to do is the following:

If the field "OriginaTable" is NOT NULL Then Check the field TableName and see if it has one of the below at the start of the value

'dbo.'

'Dimension.dbo,'

If it has then remove this from and use the value after this point, IF it does not have these values then just use the value from the field TableName.

For some reason it is still keeping the values 'dbo.' and 'Dimension.dbo.' in my new field ( NewTableName )

Thanks

[YoussefBelloum]

Hi,

try this:

IF(len(trim(OriginalTable))=0,

IF(LEFT(TableName,4) = 'dbo.',TextBetween(TableName,'.','') ,

IF(LEFT(TableName,14) = 'Dimension.dbo.',mid(OriginalTable,Index( OriginalTable,'.',2)+1)

)),TableName) as NewTableName

[sasiparupudi1]

May be try

SubField(TableName,'.',-1) as NewTableName

[sunny_talwar · Accepted solution]

May be this

IF(IsNull(OriginalTable),

IF(Left(Trim(TableName), 4) = 'dbo.',Mid(Trim(TableName), 5),

IF(Left(Trim(TableName), 14) = 'Dimension.dbo.',Mid(Trim(TableName),15)

, TableName))) as NewTableName

[ivandrago]

Hi Youssef,

I have tried your formula but it does not seem to work, if you look at the attached I am not expecting to see values in the new field if the field OriginalTable has a value in it and where it hasn't I still want to show the value from the field TableName even if it doesn't meet the 'dbo.' and 'Dimension.dbo' requirements.

Thanks

[YoussefBelloum]

My bad, I didn't pay enough attention to it, and I made the opposite..

try this:

IF(len(trim(OriginalTable))<>0,

IF(LEFT(TableName,4) = 'dbo.',TextBetween(TableName,'.','') ,

IF(LEFT(TableName,14) = 'Dimension.dbo.',mid(OriginalTable,Index( OriginalTable,'.',2)+1)

)),TableName) as NewTableName

[ivandrago]

Still seeing values in the new field if there is a value in the OriginalTable, I'm expecting the new field not have a value there.

[sunny_talwar]

Did you get a chance to try this?

IF(IsNull(OriginalTable),

IF(Left(Trim(TableName), 4) = 'dbo.',Mid(Trim(TableName), 5),

IF(Left(Trim(TableName), 14) = 'Dimension.dbo.',Mid(Trim(TableName),15)

, TableName))) as NewTableName

[vvira1316]

May be it is following

IF(IsNull(OriginalTable),
IF(Left(Trim(TableName), 4) = 'dbo.',Mid(Trim(TableName), 5),
IF(Left(Trim(TableName), 14) = 'Dimension.dbo.',Mid(Trim(TableName),15)
, TableName)), TableName) as NewTableName
;