Derived Column Question

Question

Derived Column Question

jscot91

SSC Veteran

Points: 294
More actions
January 14, 2011 at 8:12 pm

#157393

I want to know where i am wrong
Here is my source filed data
Y
N
EMPTY
UN DECIDED
Note:- Requirement is if the field has Y convert to 'YES', N convert to 'NO', Empty convert to 'Pending' and Un Decided should be as it as
I am using Derived Column and below are function that i am using
([Table1] == "Y" ? "YES" : ([Table1] == "N" ? "NO" :([Table1] == " " ? "PENDING" :"")))
and i map this field to my target field
RESULT SHOULD BE:-
YES
NO
PENDING
UN DECIDED
But I am getting this Result
YES
NO
EMPTY
EMPTY
I want to where I am wrong. Thanks for help.

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

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

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 1

What is EMPTY? Is it an empty string? A single space? Multiple spaces?

In the case it is an empty string, you better trim the column first and then check if the length of the string is equal to zero.

That way you will be 100% certain it is an empty string.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

An alternative is to create a mapping table in SQL Server and use a LOOKUP to get the result you need. Much easier to maintain and handles easily the addition of more values.

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 3

Phil Parkin (1/15/2011)
An alternative is to create a mapping table in SQL Server and use a LOOKUP to get the result you need. Much easier to maintain and handles easily the addition of more values.

That is an excellent suggestion.

@jscot91: if you implement this suggestion, make sure that when you configure your lookup component you trap the "lookup no match output" and give some meaning to that.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

stricknyn SSCertifiable Points: 6560 More actions · Answer 4

I believe you are using AND qualifiers instead of OR. With AND all conditions must be true. Looks like you should be using an OR qualifier:

([Table1] == "Y" ? "YES" : ([Table1] == "N" ? "NO" [Table1] == " " ? "PENDING" :"")))

Should be:

([Table1] == "Y" ? "YES" || ([Table1] == "N" ? "NO" || [Table1] == " " ? "PENDING" :"")))

Thanks,

Strick

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 5

stricknyn (1/17/2011)
I believe you are using AND qualifiers instead of OR. With AND all conditions must be true. Looks like you should be using an OR qualifier:
([Table1] == "Y" ? "YES" : ([Table1] == "N" ? "NO" [Table1] == " " ? "PENDING" :"")))
Should be:
([Table1] == "Y" ? "YES" || ([Table1] == "N" ? "NO" || [Table1] == " " ? "PENDING" :"")))
\

By replacing the : with the ||, you are breaking the syntax of the SSIS IIF --> (boolean expression) ? expression_if_true : expression_if_false

The solution presented by Phil solves the problem more elegantly with high maintainability.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP