Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
May be try
SubField(TableName,'.',-1) as NewTableName
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
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
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
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.
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
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
;