Post reply

adding columns using derived column transformation

  • May 13, 2008 at 4:47 pm

    #186482

    hi guys,

    i have a question regarding the use of expressions in the derived column transformation of ssis,

    i have about 4 columns ,two of them are needed for this expression

    basically here are the columns

    column: A (useless)

    column: B (useless)

    column: trueORfalse

    column: Value

    i want to add two more columns to this table using derived column transformation i.e

    new_column_total_true

    and

    new_column_total_false

    and the VALUE(one row) under these new columns will be the sum of Value when it is true / false

    for ex. if Value is 10,20 when trueORfalse is T then the value under new_column_total_true column should say 30

  • May 13, 2008 at 6:06 pm

    #814891

    Simple case statement should work.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 13, 2008 at 6:43 pm

    #814906

    CAST STATEMENT? which transformation do i have to use for this

    thanks

  • May 13, 2008 at 9:25 pm

    #814925

    thenepatsrule (5/13/2008)

    CAST STATEMENT? which transformation do i have to use for this

    thanks

    You'll need two different pieces. One is you need to use a derived column task so set up the columns that tracks the value when true and the value when false. You will need to look under the operators for conditional operator (looks like C# or c++ notation)

    You willl end up with something looking like

    (column3==True)?Value:0

    as new_column_true_detail

    (column3==True)?0:Value

    as new_column_false_detail

    The second step is to then add those up. Add an aggregate task, and set those derived columns to be sums (and set up the group by criteria, etc...)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply