How to write an if statement in Information Designer when adding an element

[Jamie Naetzker]

Hello, I was hoping to add a calculated element in my information link. My engineer prefers a specific syntax for well alias, different from the alias given in the database. I have "Lease" for %1, "Well_No" for %2, and "Alias" for %3. I want to write a statement like this, but Spotfire doesn't like this syntax:

If %2 Is Null Then %3 Else %1 & " "& %2

Concatenation of "Lease" and "Well_No" (with a space in between)unless Well_no is null, then just use the Alias. Could anyone tell me the correct syntax for this Thanks.

[Jason Joslin 3]

Hi,

I have used Spotfire Information Designer before, but don't currently have access to a setup so I can't test my solution, but I think it will work.

I notice you are using OFM, which is drawing from an Oracle database. The Information Designer is allowing you to specify a statement which will be sent to that database (I believe), so it will need to be in the syntax of Oracle (and the correct version, however your case it is fairly simple).

Try using:

CONCAT(%1, %2)

So,

IF%2 ISNULLTHEN %3 ELSE CONCAT(%1, %2)Regards,

Jason

[Jamie Naetzker]

Thanks for your response, Jason.  Unfortunately this did not work either.  Spotfire does not like the If syntax.  I subsequently pared it down as much as possible, even though this is not what I ultimately want to accomplish.  With Lease = %1, Well_No = %2, and Alias = %3, I wrote "If %2 Is Null Then %3 Else %1" and it doesn't even like that.

 

 

[Jason Joslin 3]

Oh, I thought the IF statement would be ok. Did you try finishing with an 'END IF' Anyway, I believe this is more for doing aggregations which can be found on this page in the documentation. 

 

I suggest instead editing the SQL statement that is created for this information link in the Information Designer. There you will be able to execute the IF statement, but you will need to reference the column names instead of using the %1, %2, etc.

[Stuart Wise]

Possibly a bit late on this, but it looks like you are partially using IF statement lingo and partially CASE Statement lingo

Try :-

CASE WHEN %2 is null THEN %3 ELSE %1 & " "& %2 END