Forum Discussion

brunofrancesco's avatar
brunofrancesco
Frequent Visitor
8 years ago

ETL fixing Phone Numbers errors

Hi,   It`s a challenge for me to find a simple way to fix a input numbers. In this scenario there are a lot wrong inputs the cases are :   (view: in the same column and different rows) Number ...
Need Help
brunofrancesco's avatar
brunofrancesco
Frequent Visitor
8 years ago

Thanks for the help!

 

I Write this Code:

 

Fixed Phone =
VAR phone = [customer_contact]
VAR LenContact = LEN(phone)
VAR Num8dig = LEFT(RIGHT(phone;8);1)
VAR CountryCode = 55
VAR CityCode = 22
RETURN
SWITCH(True();
LenContact<8;phone;
LenContact=8&&Num8dig in {"7";"6";"5";"4";"3";"2";"1";"0"}; CountryCode & CityCode & phone;
LenContact=8&&Num8dig in {"9";"8"}; CountryCode & CityCode &"9"&phone;
LenContact=9&&Num8dig in {"9";"8"}; CountryCode & CityCode & phone;
LenContact=10&&Num8dig in {"7";"6";"5";"4";"3";"2";"1";"0"};CountryCode & phone;
LenContact=10&&Num8dig in {"9";"8"}; CountryCode & LEFT(phone;2)&"9"& RIGHT(phone;8);
LenContact=12&&Num8dig in {"7";"6";"5";"4";"3";"2";"1";"0"}||LenContact=13;phone;
LenContact=12&&Num8dig in {"9";"8"}; LEFT(phone;4)&"9"& RIGHT(phone;8);
LenContact=13;phone;
LenContact>13&&Num8dig in {"9";"8"};RIGHT(phone;13);
LenContact>13&&Num8dig in {"7";"6";"5";"4";"3";"2";"1";"0"};RIGHT(phone;12)
;0)

 

but:

 "Expressions that generate variable data type can not be used to define calculated columns."

 

 

Seward12533's avatar
Seward12533
Solution Sage
8 years ago
VAR is ok. But there must be something wrong with one of your statements. And sometimes is returning text other times numbers or something else like a Boolean. . You may need to use FORMAT to convert numbers to text or find other problem with the formula. Deconstruct and text your formula by commenting out your terms ( using // ) and introducing one at a time till you find it. Start with the Var statements for Cory and country code. They need to be inside quotes.

Fixed Phone =
VAR phone = [customer_contact]
VAR LenContact = LEN(phone)
VAR Num8dig = LEFT(RIGHT(phone;8);1)
VAR CountryCode = “55”
VAR CityCode = “22”
RETURN
SWITCH(True();
LenContact<8;phone;
// LenContact=8&&Num8dig in {"7";"6";"5";"4";"3";"2";"1";"0"}; CountryCode & CityCode & phone;
// LenContact=8&&Num8dig in {"9";"8"}; CountryCode & CityCode &"9"&phone;
// LenContact=9&&Num8dig in {"9";"8"}; CountryCode & CityCode & phone;
// LenContact=10&&Num8dig in {"7";"6";"5";"4";"3";"2";"1";"0"};CountryCode & phone;
// LenContact=10&&Num8dig in {"9";"8"}; CountryCode & LEFT(phone;2)&"9"& RIGHT(phone;8);
// LenContact=12&&Num8dig in {"7";"6";"5";"4";"3";"2";"1";"0"}||LenContact=13;phone;
// LenContact=12&&Num8dig in {"9";"8"}; LEFT(phone;4)&"9"& RIGHT(phone;8);
// LenContact=13;phone;
// LenContact>13&&Num8dig in {"9";"8"};RIGHT(phone;13);
// LenContact>13&&Num8dig in {"7";"6";"5";"4";"3";"2";"1";"0"};RIGHT(phone;12)
;0)