topic Re: Nested IF issue in QlikView

Nested IF issue

ivandrago — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Nested IF issue

YoussefBelloum — Fri, 11 May 2018 10:17:03 GMT

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

Re: Nested IF issue

sasiparupudi1 — Fri, 11 May 2018 10:32:45 GMT

May be try

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

Re: Nested IF issue

sunny_talwar — Fri, 11 May 2018 11:15:46 GMT

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

Re: Nested IF issue

ivandrago — Fri, 11 May 2018 11:52:48 GMT

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

Re: Nested IF issue

YoussefBelloum — Fri, 11 May 2018 12:12:44 GMT

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

Re: Nested IF issue

ivandrago — Fri, 11 May 2018 12:24:33 GMT

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.

Re: Nested IF issue

sunny_talwar — Fri, 11 May 2018 12:26:18 GMT

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

Re: Nested IF issue

vvira1316 — Fri, 11 May 2018 12:31:19 GMT

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
;