How would you join two tables based on two columns with same names, but different datatypes? In this example, phone_number is string in table_1 and int64 in table_2. When I try to change datatype from string to int, it changes the values!

table_1
|project name, phone_number
|join kind=fullouter table_2 on $left.name==$right.name and $left.phone_number==$right.phone_number

Thanks

CodePudding user response：

You have issues with your data to begin with.

A phone number is of type string, not an integer.
A phone number might have a leading zero, e.g., 050123456 or non-digit characters e.g., 972123456 or *1234.

If you will try to convert those strings to integer, you will get nulls.
If you convert your integers to string, you will discover that for some of the values you are missing a leading zero.

That said, in this specific case, I would recommend converting string to integer, paraphs after removing any non-digit character.

let table_1 = datatable(name:string, phone_number:string)
[
    "John"  ,"050123456"
   ,"Linda" ," 972123456"
   ,"Ben"   ,"*1234"
   ,"Pam"   ,"012-333-444"  
];
let table_2 = datatable(name:string, phone_number:long)
[
    "John"  ,50123456
   ,"Linda" ,972123456
   ,"Ben"   ,1234
   ,"Pam"   ,12333444
];
table_1
| project name, phone_number = tolong(replace_regex(phone_number, @"\D ", ""))
| join kind=fullouter table_2 on $left.name==$right.name and $left.phone_number==$right.phone_number

Fiddle