November 3, 2011 at 2:45 pm
I have a derived column that I am trying transform based on a range.
(lv <= "20") ? "1" : (lv <= "50") ? "2" : (lv <= "80") ? "3" : (lv <= "100") ? "4" : (lv <= "120") ? "5" : (lv <= "150") ? "6" : (lv <= "300") ? "7" : ""
Here is what I have so far. All values after the 4, 5, 6, and 7 do not seem to get read properly and are changed to 1 and 2.
Would a look up prove to be better due the extended amount of OR statements?
November 4, 2011 at 11:43 am
I think your issue is that you are using strings in the comparison not numbers. What is the data type of lv? If it is a number stored in a string you might want to convert it to a numeric data type and do the comparison. For example when comparing strings "100" < "20" and every 2 digit "number" is between 80 and 99 is > "100". For example "91" > "100".
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 4, 2011 at 12:05 pm
Instead of using a Derived Column Expression I used a CASE Statement after the Data load.
UPDATE DimTable
SET [rank] = CASE
WHEN [lv] BETWEEN 1 and 19 THEN 1
WHEN [lv] BETWEEN 20 and 49 THEN 2
WHEN [lv] BETWEEN 50 and 79 THEN 3
WHEN [lv] BETWEEN 80 and 99 THEN 4
WHEN [lv] BETWEEN 100 and 119 THEN 5
WHEN [lv] BETWEEN 120 and 149 THEN 6
WHEN [lv] BETWEEN 150 and 300 THEN 7
ELSE ''
END
GO
November 4, 2011 at 12:14 pm
That works. One difference is that you are using numeric data and not strings to do the comparison.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 4, 2011 at 12:21 pm
Whoops. Thanks for the other suggestion as well. It might be very handy in a future project. Always learning new tricks Thanks!
January 5, 2012 at 2:25 am
Try to substitute the derived column transformation by adding the column in the OLE DB Source form the start and set the required conditions using Case statement.
Regards,
Samer
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply