I want to add a column named CategoryCode which would take the first 3 letters of Category column + the first 3 letters of Subcategory column+ any number.
The problem is that I have some values in CategoryCode column which I don't have to change. I just have to operate on values which is null.
Can anyone help me with that? It would be of great help.
Add a comment
|
1 Answer
You can simply add a Derived Column that replaces the input CategoryCode column using the following expression:
REPLACENULL([CategoryCode],LEFT([Category],3) + LEFT([SubCategory],3) + "123")
Update 1 - Adding an auto increment number
Unfortunately, SSIS Derived Column cannot be used to add an auto increment or randomly generated number. To achieve that you need to use a Script Component transformation:
if(row.CategoryCode_IsNull == true)
row.outCategoryCode = row.Category.Substring(0,3) + row.SubCategory.Substring(0,3) + id.toString();
else
row.outCategoryCode = row.CategoryCode;
More detailed examples:
4 Comments
ADITYA NARAYAN
But I have to increase the numeric part for every record. It's like Category code would be my primary key.
Hadi
What is your data source? Are you reading from SQL? @ADITYANARAYAN
ADITYA NARAYAN
The question is like... There is one category.txt file with CategoryCode, category and subcategory column. There is another Product table with product descriptions, price, category and subcategory column. We have to merge both files on the basis of categoryCode such that Category code becomes primary key in Category table and becomes foreign key reference to Product table.
Hadi
@ADITYANARAYAN you need to use a Script Component to add an incremental number. I added several examples to my answer