Author 
Topic 

tooba
Posting Yak Master
224 Posts 
Posted  03/19/2015 : 10:53:42

Hi,
Need help. Here is my sample data
ID,Fname,CellPhone,OtherPhone,OID 1,Smith,2039131941,Null,1452 2,James,(203)9131941,2039131942,6458 3,Chris,Null,(203)9131943,1111
Here is my requirement.
If Length of CellPhone > 10 or < 10 or Null use OtherPhone If Lenght of OtherPhone > 10 or < 10 or Null use OID
So far I have this expression in derived column
ContactNumber = (Len(CellPhone) > 10  Len(CellPhone) < 10  isnull(CellPhone) ? OtherPhone : CellPhone : (Len(OtherPhone) > 10  Len(OtherPhone) < 10  isnull(OtherPhone) ? OID : OtherPhone ))
I am not getting right result what I want, at the end I want below result (as an example)
ID,Fname,ContactInfo 1,Smith,2039131941 2,James,2039131942 3,Chris,1111
Please advise.
Thank You. 

gbritton
Flowing Fount of Yak Knowledge
2780 Posts 
Posted  03/19/2015 : 11:11:50

That is not a valid expression! How is it working for you? In particular, there are two colons in the expression making it look like a threeway conditional expression, but there is no such thing.
e.g. this section is not valid:
... ? OtherPhone : CellPhone : ...
and will not compile. Please post the EXACT expression from the DC component. 


tooba
Posting Yak Master
224 Posts 
Posted  03/19/2015 : 11:54:07

Sorry, here is the exp...
Len(CellPhone) > 10  Len(CellPhone) < 10  isnull(CellPhone) ? OtherPhone : (Len(OtherPhone) > 10  Len(OtherPhone) < 10  isnull(OtherPhone) ? OID : OtherPhone 


gbritton
Flowing Fount of Yak Knowledge
2780 Posts 
Posted  03/19/2015 : 12:03:57

OK  I simulated this. using your DC expression and data from your first line, for your first row I got:
ID,Fname,ContactInfo 1 ,Smith,1452
What do you actually wan to see? 


tooba
Posting Yak Master
224 Posts 
Posted  03/19/2015 : 12:06:02

I want to see
ID,Fname,ContactInfo 1,Smith,2039131941 2,James,2039131942 3,Chris,1111
Thank You for your help! 


gbritton
Flowing Fount of Yak Knowledge
2780 Posts 
Posted  03/19/2015 : 12:18:02

OK  so the expression says:
if the cell phone number is null or not exactly 10 digits, then if the other phone is null or not exactly 10 digits, choose the OID.
In the first row, the cell phone number IS exactly 10 digits and the other phone IS null, so the OID is selected.
In other words, your expression does exactly what you said in words:
quote:
If Length of CellPhone > 10 or < 10 or Null use OtherPhone If Lenght of OtherPhone > 10 or < 10 or Null use OID
However, your desired output does NOT match the above conditions. I think you need to clarify your conditions. 


tooba
Posting Yak Master
224 Posts 
Posted  03/19/2015 : 12:25:16

Here is my source data (as an example)
ID,Fname,CellPhone,OtherPhone,OID 1,Smith,2039131941,Null,1452 2,James,(203)9131941,2039131942,6458 3,Chris,Null,(203)9131943,1111
I want, If Length of CellPhone > 10 or < 10 or Null Use OtherPhone, ( In first row Len of CellPhone is not > 10 or < 10 or Null, so it should Pick 2039131941) Second Row, CellPhone is Null and len of OtherPhone is not > 10 or < 10 or Null, so it should pick 2039131942 Third Row, CellPhone is Null and Len of Other Phone is > 10 , so it should Pick 1111
Please guide me, if I am missing something. My expression is not 100% right that's why I am getting wrong data. I need help to fix my expression.
Thank You.



gbritton
Flowing Fount of Yak Knowledge
2780 Posts 
Posted  03/19/2015 : 13:43:45

But your expression does not return CellPhone in any of the branches:
Len(CellPhone) > 10  Len(CellPhone) < 10  isnull(CellPhone) ? OtherPhone : (Len(OtherPhone) > 10  Len(OtherPhone) < 10  isnull(OtherPhone) ? OID : OtherPhone
so since the first row has a cellphone that is 10 digits, it takes the "else" branch:
(Len(OtherPhone) > 10  Len(OtherPhone) < 10  isnull(OtherPhone) ? OID : OtherPhone
Here, OtherPhone is null so it takes OID.
How about just:
LEN(CellPhone) == 10 ? CellPhone : LEN(OtherPhone) == 10 ? OtherPhone : OID 


tooba
Posting Yak Master
224 Posts 
Posted  03/19/2015 : 13:58:09

I test your exp
LEN(CellPhone) == 10 ? CellPhone : LEN(OtherPhone) == 10 ? OtherPhone : OID
First and second rows looks good, third row I am getting Null. 


gbritton
Flowing Fount of Yak Knowledge
2780 Posts 
Posted  03/19/2015 : 14:16:15

LEN(CellPhone) == 10 && !ISNULL(CellPhone) ? CellPhone : LEN(OtherPhone) == 10 && !ISNULL(OtherPhone) ? OtherPhone : OID 


tooba
Posting Yak Master
224 Posts 
Posted  03/19/2015 : 14:31:30

Looks great. Thank You for your Help!!
This question answered by gbritton last reply. 



Topic 


