Filters?

[Stevennn]

Hello Guys,
if u can help that would be great!
working with relational model.
got a column with amount: would like to split it into two columns.
one contains '-%' and the 2nd not contains

Set it as a filter, works only for one condition but not for both.

What should I do?

Thank-you!

 

[blom0344]

You don't want a filter here, as you do not want to restrict the dataset itself.
You need to create 2 new dataitems that use a CASE construct to split the original column:

CASE WHEN
[ORIGINAL_COLUMN] <<EXPRESSION>>
THEN  [ORIGINAL_COLUMN] ELSE ('-') END

However, you say the amount can contain '-%' , so is this a value stored as a string?

Ties Blom

 

[Stevennn]

Hi Bloom,

Thank-you for helping!
I have an amount field, and i want to break it down into credits and debits.
I looked at the data source, this is what i see:
AMT
-183743.34
834754.23
-343545.67
435454.99
Does this make a difference?

That is why i thought of pulling it by contains '-%' and not contains '-%'.

Thank-you!

 

[blom0344]

Are you kidding? Are you going to evaluate negative values that way? Why not old-fashioned testing whether the value is <0 or >0?
You're dealing with numeric values , not string (I presume),
are you?

Ties Blom

 

[Stevennn]

, wow, i dont know what i was thinking! that was pretty stupid of me.
Yeah im dealing with numeric!

i should still use the case statment?

So i will create two data item in the query:
CASE WHEN
[AP].[AMOUNT] < '0'
THEN [AP].[AMOUNT] ELSE > '0' END

?

 

[Stevennn]

got it to work, thank-you Bloom!