Post reply

Derived colum expression with strings removing 0

  • April 12, 2019 at 2:09 pm

    #3157423

    Hi, I am setting up a derived column to combine a variable that is entered to a previous derived column with a dataflow value.

    (DT_I4)@[User::Actual_FileName] in this example = 1000 and becomes the derive column called TMC_Ident

    Branch Key = '072'

    In turn,

    (DT_STR,4,1252)TMC_Ident + (DT_STR,6,1252)Branch_key should therefore = 1000072 but is coming out from this expression as 100072 i.e. removing the leading 0 from the value.

    Any ideas on how to avoid this?

    Should I change (DT_I4)@[User::Actual_FileName] to be (DT_STR,4,1252)User::Actual_FileName] ?

    Cheers

    Dave

  • April 12, 2019 at 2:36 pm

    #3158468

    I assume Branch_key is some kind of numerical data type? Numerical data types don't store leading zeros. Assuming that Branch_key should have 6 characters (as you're casting to a (DT_STR,6,1252) the you could use something like this:

    (DT_STR,4,1252)TMC_Ident + RIGHT("000000" + (DT_STR,6,1252)Branch_key,6)

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • April 12, 2019 at 3:19 pm

    #3160309

    Hiya, thanks for the response.

    i had cast the branch_key as varchar 3 in the select statement but the advanced editor was still showing 4 byte int - all fixed when that was changed.

    Always check the advanced editor people!

    have a great weekend,

    Dave

Viewing 3 posts - 1 through 2 (of 2 total)

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