How do I connect to another table with multiple delimited values in the same column?

[t_guet01]

Hey guys,

I am facing an issue that I would like to connect customer data to a transaction table. Within the transaction table, everything is given as expected. However, the customer data are not unambiguous. More specifically, I find multiple CustomerIDs in the same cell per customer. The customer IDs are delimited mostly by "/" (sometimes even ";"). During the years, some customers got new IDs. Within the transaction table, any Customer ID could be given.

Example:

Customer data:

Customer ID	Customer Name
A123	A-Corp
B456 / B789	B-Company
C123 / C456 / C789	C-Limited
D987 ; D654	D-Group

Transaction data:

OrderID	CustomerID	InvoiceAmount
100078	A123	100
100079	B456	200
100080	B789	50
100081	D654	300

In this example, customer "B-Company" has two possible customer IDs (given in the customer table) which both appear in the transaction table. I would like to match both transaction lines to "B-Company" to show an aggregated sum.

How could I link the customer data to the transaction data?

Thanks in advance and best

Thomas

[chrismarlow]

Hi,

Maybe try something like this;

data:
load
	Trim(SubField([Customer ID], ';')) as [Customer ID],
	[Customer Name]
;
load
	Trim(SubField([Customer ID], '/')) as [Customer ID],
	[Customer Name]
;
load * inline [
Customer ID,	Customer Name
A123, A-Corp
B456 / B789, B-Company
C123 / C456 / C789, C-Limited
D987 ; D654, D-Group
];

Cheers,

Chris.