Hi All,

I'm using Cognos v11.2.3, and I have a compound text data item that only ever has three variable values - "AB", "AB,CD", or "CD,AB".  I want an expression that evaluates that data item and always results in outputting "AB".  I know how to write the independent expressions to get to either of the two "AB" results if for example the variables were only "AB,CD" or "AB" OR just "CD,AB" or "AB".  However, I don't know how to format the larger, combined expression.

I think the below expression would render "AB" when the two variable choices are only "AB,CD" or "AB":

Case substring([data_item_name],4,2)
When 'CD' then substring([data_item_name],1,2)
else [data_item_name]

And, I believe this one would work to render "AB" when the only two choices are "CD,AB" or "AB":

Case substring([data_item_name],1,2)
When 'CD' then substring([data_item_name],4,2)
else [data_item_name]

What is the single combined expression which will account for all three variables?

Thanks for helping me with my ands/ors, and ifs/elses.

Quote from: FerdH4 on 31 Jan 2024 03:48:57 PMHi All,

I'm using Cognos v11.2.3, and I have a compound text data item that only ever has three variable values - "AB", "AB,CD", or "CD,AB".  I want an expression that evaluates that data item and always results in outputting "AB".  I know how to write the independent expressions to get to either of the two "AB" results if for example the variables were only "AB,CD" or "AB" OR just "CD,AB" or "AB".  However, I don't know how to format the larger, combined expression.

I think the below expression would render "AB" when the two variable choices are only "AB,CD" or "AB":

Case substring([data_item_name],4,2)
When 'CD' then substring([data_item_name],1,2)
else [data_item_name]

And, I believe this one would work to render "AB" when the only two choices are "CD,AB" or "AB":

Case substring([data_item_name],1,2)
When 'CD' then substring([data_item_name],4,2)
else [data_item_name]

What is the single combined expression which will account for all three variables?

Thanks for helping me with my ands/ors, and ifs/elses.

Hi,

If I'm understanding correctly, the item can contain only:

AB
AB,CD
CD,AB

The case syntax you are currently using is "simple case" - one of two possible forms. The other is "complex case". You really need the latter for this:

case
  when substring([data_item_name],1,2) = 'AB'
    then substring([data_item_name],1,2)
  when substring([data_item_name],3,2) = 'AB'
    then substring([data_item_name],3,2)
  else null
end

Cheers!

MF.

Quote from: MFGF on 02 Feb 2024 07:35:52 AMHi,

If I'm understanding correctly, the item can contain only:

AB
AB,CD
CD,AB

The case syntax you are currently using is "simple case" - one of two possible forms. The other is "complex case". You really need the latter for this:

case
  when substring([data_item_name],1,2) = 'AB'
    then substring([data_item_name],1,2)
  when substring([data_item_name],3,2) = 'AB'
    then substring([data_item_name],3,2)
  else null
end

Cheers!

MF.

Yep - you've got the use case correct MF.

Though I didn't know the syntax, I can see that I was also trying to over complicate the use case by thinking I needed to test for the third condition of "AB" as well!

I'll give that expression a try and report back.

Quote from: MFGF on 02 Feb 2024 07:35:52 AMHi,

If I'm understanding correctly, the item can contain only:

AB
AB,CD
CD,AB

The case syntax you are currently using is "simple case" - one of two possible forms. The other is "complex case". You really need the latter for this:

case
  when substring([data_item_name],1,2) = 'AB'
    then substring([data_item_name],1,2)
  when substring([data_item_name],3,2) = 'AB'
    then substring([data_item_name],3,2)
  else null
end

Cheers!

MF.

That expression did rid me of any compound variables.  However, I think I do need to account for the condition of the simpler variable "AB" because with the statement above, I ended up with a lot of rows with the that data item appearring as blank which were not previously blank (before inserting the expression).   

Do I need a final Else...DOH!...of course I do.  I see that you offered me "null".  I think I want the variable name at the end of that Else.  I'll go re-retest and report back.

Okay, this expression worked better for me:

case
  when substring([data_item_name],1,3) = 'AB,'
    then substring([data_item_name],1,2)
  when substring([data_item_name],3,3) = ',AB'
    then substring([data_item_name],3,2)
  else [data_item_name]
end

In essence, I decided to test for the inclusion of the "," (comma) because it gave me added confidence that I was working with a compound value coming from the source.

Your expression also helped me to uncover that there is a fourth value of "CD".  I'll have to ask someone who knows that data what the heck that condition represents and what they want done with same.

But, I'd call this mystery "Resolved."