Link the slightly different Keys of two tables together/ second table has prefix number

[Applicable88]

Hello,

I have a KEY that I want to use connecting two tables with:

Table 1			Table2
Shelf No.	Material	Area	Error Report No.	Description
7894	fsfdafsdfsd	Sector2	11111111	(9910)7894
6666	sdfdsfs	Sector33	22222222	(9910)6666
1234	dfsdsfsfs	Sector1	33333333	(9910)1234
7845	sdfdsfdd	Sector65	4444444	(9910)7845

As you can see I have a Table 1 with Shelf ID and the Location of the shelf. Table2 is an error report. In column "description" we told workers to scan the QR- Code of the specifi shelf, where trouble  occurred. Unfortunately the barcode-scanner also always scans the prefix (9910) of the QR-Code as well.  So even though incident report of (9910)7845 of table2 is referring to 7845 of table1, I don't get the 2 keys linked with each other. 

Now I have problem connecting these two tables with that key. Is there a kind of workaround or "ignoring" the (9910) prefix? Note, that Table2 is a SAP derived table which contains alls sorts of errors reports inside the company and not only shelf related reports. For instance in the "description" field could also be a plain text like "Line34 Malfunction". 

Hope someone has a solution for me.

Stay safe!

Thanks in advance. 

[Applicable88]

I have a KEY that I want to use connecting two tables with:

Table 1			Table2
Shelf No.	Material	Area	Error Report No.	Description
7894	fsfdafsdfsd	Sector2	11111111	(9910)7894
6666	sdfdsfs	Sector33	22222222	(9910)6666
1234	dfsdsfsfs	Sector1	33333333	(9910)1234
7845	sdfdsfdd	Sector65	4444444	(9910)7845

As you can see I have a Table 1 with Shelf ID and the Location of the shelf. Table2 is an error report. In column "description" we told workers to scan the QR- Code of the specifi shelf, where trouble  occurred. Unfortunately the barcode-scanner also always scans the prefix (9910) of the QR-Code as well.  So even though incident report of (9910)7845 of table2 is referring to 7845 of table1, I don't get the 2 keys linked with each other. 

Now I have problem connecting these two tables with that key. Is there a kind of workaround or "ignoring" the (9910) prefix? Note, that Table2 is a SAP derived table which contains alls sorts of errors reports inside the company and not only shelf related reports. For instance in the "description" field could also be a plain text like "Line34 Malfunction". 

Hope someone has a solution for me.

Stay safe!

Thanks in advance. 

[Nicole-Smith · Accepted solution]

Add another column when you're loading table 2 and remove the first part so the two columns match:

SUBFIELD(Description, ')', 2) AS [Shelf No.]

[Applicable88]

Hello Nicole,

thanks for the quick reply. But somehow I don't think it will work out that way.  Because the Values that I can get in "description" column can also contain other irregular forms other than (9910)XXXX.

What will it do with values like "Manufacturing Line Broken" and similar content?

[martinpohl · Accepted solution]

is the part of the shel ID always 4 digits?

So create link field

right(description,4) as ShelfID

Regards

[bhaskar_sm · Accepted solution]

Hi,

I would say you need to find the pattern for the table 2 description fields and then segregate the numbers values by creating an additional columns in table 2 and then you can link the new calculated columns with table 1.  Kindly share the possible no fields values that you might get in a description filed so that we can make the working key fields based on the values.

Thanks ,

BK