Forum Discussion
Fill with digits
Hello everyone,
I have a table "Customers_and_Suppliers" that contains an ID and the corresponding Customers_and_Suppliers_NR. I want to add a custom column in this table. If there is a NR that matches the Supplier ID(secondtable), it should be filled with a maximum of 10 digits. There should be a 2 in the front left and the rest with 0. If there is no match, the same should be filled with a 1 and the rest with 0. Here's an example:
Initial situation:
| ID | Customers_and_Suppliers_NR | |
| 3087 | 1 | |
| 946 | 30 | |
| 2642 | 3 | |
| 28888 | 10 | |
| 2 | 20 | |
| 44 | 4 | |
| 983764 | 2 | |
| 19 | 20 | |
| 222 | 40 | |
| 99999 |
| 5 |
Supplier:
| ID |
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
Result:
| ID | Customers_and_Suppliers_NR | AdressID | ||
| 3087 | 1 | 1000003087 | ||
| 946 | 30 | 2000000946 | ||
| 2642 | 3 | 1000002642 | ||
| 28888 | 10 | 2000028888 | ||
| 2 | 20 | 2000000002 | ||
| 44 | 4 | 1000000044 | ||
| 983764 | 2 | 1000983764 | ||
| 19 | 20 | 2000000019 | ||
| 222 | 40 | 2000000222 | ||
| 99999 |
| 5 | 1000099999 |
Do you have an idea how I can solve this?
4 Replies
Hi
Try the calculate column below
Adress ID = if( Customer_and_Suppliers_NR in {"10","20","30","40","50"},2000000000 + VALUE(ID),1000000000 + VALUE(ID))
or
Adress ID = if( Customer_and_Suppliers_NR in {"10","20","30","40","50"},
REPLACE ("2000000000",10- LEN(ID) + 1,LEN(ID),ID coummn),REPLACE ("1000000000",10- LEN(ID) + 1,LEN(ID),ID coummn) )
