Derived column transformation

Question

Derived column transformation

domiemutai

SSC Enthusiast

Points: 195
More actions
May 13, 2010 at 7:19 am

#143058

I have a database for a Survey where responses are coded in terms of numbers ie 1 for 'Male', 2 for 'Female' in Sex. I used a derived column transform to recode the variables to what the codes represent in the below expression
Sex == 1 ? "Male" : "Female"
How can i recode more than 5 values say for Religion, 1=Catholic,2=Anglican,3=Baptist,4=Orthodox, 5=Other protestant, 6 = None, 7 =Unknown.
Any help will be appreciated.
Thanks;
Dominic Mutai

Viewing 7 posts - 1 through 6 (of 6 total)

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

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

domiemutai (5/13/2010)
I have a database for a Survey where responses are coded in terms of numbers ie 1 for 'Male', 2 for 'Female' in Sex. I used a derived column transform to recode the variables to what the codes represent in the below expression
Sex == 1 ? "Male" : "Female"
How can i recode more than 5 values say for Religion, 1=Catholic,2=Anglican,3=Baptist,4=Orthodox, 5=Other protestant, 6 = None, 7 =Unknown.
Any help will be appreciated.
Thanks;
Dominic Mutai

It's easier if you build a lookup table in SQL Server (Code, Description) and then use a select ... join statement as the source for your dataflow. Then you cope with any number of codes very easily (and quickly).

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

I would tend to agree with Phil. However, it depends on the source of the data. For things like flatfile sources or sources you can't change you might consider a lookup transform, with that few of values it would cache the lookup data and be pretty fast.. I would caution against the derived column transform only because when you go to 3 or 4 or more possible cases it starts to get very messy. And in the lookup transform you can build the data right there with a statement like:

SELECT Id = 1, RelDesc = 'Catholic'

UNION ALL

SELECT 2, 'Anglican'

UNION ALL

SELECT 3, 'Baptist'

UNION ALL

SELECT 4, 'Orthodox'

UNION ALL

SELECT 5, 'Other protestant'

UNION ALL

SELECT 6, 'None'

UNION ALL

SELECT 7, 'Unknown'

CEWII

Dan.Humphries SSChampion Points: 12125 More actions · Answer 3

It is likely just becuase I am stuburn and stuck in my ways but when you are talking about such a small amount of changes. I still prefer to write a SQL statement that pulls the data the way I want. using a case statement to substitute the values I expect for an output.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 4

How can i recode more than 5 values say for Religion, 1=Catholic,2=Anglican,3=Baptist,4=Orthodox, 5=Other protestant, 6 = None, 7 =Unknown.

You can use a Switch case as an EXP in derived Column Like

Region == 1 ? "Catholic" : Region ==2 ? "Anglican" : Region == 3 ? "Baptist" :Region ==4 ? "Orthodox" : Region == 5 ? "Other Protestant" : Region == 6 ? "None" : "Unknown"

? - If and : - else

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

divyanth (5/13/2010)
How can i recode more than 5 values say for Religion, 1=Catholic,2=Anglican,3=Baptist,4=Orthodox, 5=Other protestant, 6 = None, 7 =Unknown.
You can use a Switch case as an EXP in derived Column Like
Region == 1 ? "Catholic" : Region ==2 ? "Anglican" : Region == 3 ? "Baptist" :Region ==4 ? "Orthodox" : Region == 5 ? "Other Protestant" : Region == 6 ? "None" : "Unknown"
? - If and : - else

Actually, I was warning against this method. I think it makes it less readable. Mileage may vary..

CEWII

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 6

I understand Elliot, i replied to post only to say that he has an option of writing a switch case Exp also, if he wants to and if performance is the key factor then i would do as you/phil suggested..