Topic
3 replies Latest Post - ‏2013-02-04T21:00:30Z by SystemAdmin
SystemAdmin
SystemAdmin
1959 Posts
ACCEPTED ANSWER

Pinned topic Removing Null values from list of values in Framework or Cognos 10 RS

‏2013-01-31T20:54:19Z |
Hello! I'm popping out these questions!
I have a question regarding SQL in Cognos 10 or Framework. I am attempting to creating a new Expression within Framework, in order to denormalize some data.
The current Expression has the values 1,2,3,4 in it. I want to create ValueA that shows 1,2 and ValueB that shows 3,4.
When I do this, ValueA shows 1,2,NULL,NULL and ValueB shows NULL,NULL,3,4.
My formulas are as follows:
ValueA: If(Value Contains(1) OR Value Contains(2)) Then (Value) Else(Null)
ValueB: If(Value Contains(3) OR Value Contains(4)) ThenValue) Else(Null)

I don’t want to show NULL values within my list of values, I only want it to show 1,2 for ValueA and 3,4 for ValueB
Do you know how I can accomplish this inside of SQL coding, or is there a setting within Framework?
Thanks, Take care
Updated on 2013-02-04T21:00:30Z at 2013-02-04T21:00:30Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1959 Posts
    ACCEPTED ANSWER

    Re: Removing Null values from list of values in Framework or Cognos 10 RS

    ‏2013-01-31T20:57:36Z  in response to SystemAdmin
    I should explain that the 1,2,3,4 is from a table, and by creating these new expressions, I am wanting them to act like new tables.
    So the only values to come from ValueA should be 1,2 (without any nulls) and ValueB should be 3,4 (without any nulls)
    • DanielWagemann
      DanielWagemann
      850 Posts
      ACCEPTED ANSWER

      Re: Removing Null values from list of values in Framework or Cognos 10 RS

      ‏2013-02-04T17:58:23Z  in response to SystemAdmin
      Maybe you could try something like:

      QuerySubject: Main

      1
      2
      3
      4

      QuerySubject: 1and2Only
      If then else
      1
      2
      NULL
      NULL
      Filter Null or a 1:1 relationship to the Main Query Subject

      QuerySubject: 3and4Only
      If then else
      NULL
      NULL
      3
      4
      Filter Null or a 1:1 relationship to the Main Query Subject
      • SystemAdmin
        SystemAdmin
        1959 Posts
        ACCEPTED ANSWER

        Re: Removing Null values from list of values in Framework or Cognos 10 RS

        ‏2013-02-04T21:00:30Z  in response to DanielWagemann
        you gave me an idea from your answer. What I ended up doing was creating 2 queries, and filtered out the nulls. Then I did a join on a common object, with a 1:1 and 0:1 and it worked perfectly. Thanks man.