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

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

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

    ‏2013-01-31T20:57:36Z  
    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

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

    ‏2013-02-04T17:58:23Z  
    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)
    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

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

    ‏2013-02-04T21:00:30Z  
    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
    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.