Exclude Nulls in Crosstab Rows

[priya@90]

Hello Friends

I am doing Union and Query1 as Varchar data types, Date Data types and Integer data type columns, 2nd query i have created data item expression for Varchar Data types as ' '(quotes), Date data type is 1999-12-31(default one in db2) and Integer data type expression as: cast(null as integer).

when i am dragging Varchar columns into Rows i am getting Nulls and values, i have applied suppression on columns as well.
How to remove those nulls in rows?. Please find the screenshot to understand better. Please advise you suggestions.

Wherever iam using dummy items as in ' '(quotes) and it's reflecting in output as blanks. I don't how to resolve this issue, if anybody had an experience please share.

i am using nested Rows(20) and Columns

Cognos 10.2.1 and Db2

Thanks,

[sdf]

Have you played around using slicers or the except () function???

cheers!

[priya@90]

Thanks your Reply SDF and i am using relational model.

[Deep750]

Hi Priya,
I believe that is exactly how a union should work...Can you please explain what you are trying to accomplish by using the union?

[priya@90]

Hi Deep,
Thanks for ur reply I know it will come like that, is there anyway to reduce nulls

Query1:           Query2:
---------           ----------
1,2,3,...10        1,2,3,4,......11,12,...20

Output is: 1,1,2,2,3,3,4,4....null 11, null 12...null 20 but final output 1,2,3,4,5....11,12,13....19,20.  i should not see any blanks.

Thanks,

[Deep750]

What is the reason for using union? Maybe there's some other way to accomplish the same?

Isn't the quotes with a blank between? meaning it is a string there, not null.

[hespora]

you want to look at the coalesce() function.

[Invisi]

Not only coalesce, but perhaps also a join instead of a union?