I have two seperate data sources which have different information that I need to relate to each other.
Now the problem I have is that Table 1 has part description is an annoying format which contains the part number I want to relate.
Example :Table1::Part Description = "BIG THINGY PN 344400" where as Table2::PartNumber = 344400
We have a mapping table that we use throughout the project and was hoping to somehow use this (unfortunately by the previous owner who no longer works for us)
I've created a tab within our mapping spreadsheet to try and convert the 'Part Description' from Table 1 to create a new Field, Just substituing out the last couple of characters. Unfortunately for different parts, there is a different length of part numbers so I am manually altering this.
My Mapping Table looks like the below:
|Part Description||Part Number Converted (from Part Description)|
|BIG THINGY PN 222000||222000|
|OTHER THINGY SN 39002HHB2||39002HHB2|
|BLAEXAMPLE PN 3N3EE741000012P01||3N3EE74100012P01|
The data from Table 1 is downloaded daily, where as Table 2 is extremely rarely updated.
So my actual question!!
Is it possible, for Table1::PartDescription to be uploaded as the new MappingTable::PartNumberConverted instead using the likes of ApplyMapping?
If this doesn't make sense please just let me know and I will try to explain the best I can.
Mapping Load [Part Description],
SubField([Part Description], ' ', -1) as LastName_Part_Description,
Load [Part Number],
Load *, MapSubString('Table2', LastName_Part_Description, [Part Number]) as LastName_Part_Description
load text(replace( "Part Description",left("Part Description",limit-1),'') )as newField;
load *,FindOneOf("Part Description", '1234567890') as limit Inline [
BIG THINGY PN 222000
OTHER THINGY SN 39002HHB2
BLAEXAMPLE PN 3N3EE741000012P01
Struggling to get the last bit working.
Can you please advise what your trying to do in the MapSubString.
It doesn't like having the underlined part number:
MapSubString('Table2', LastName_Part_Description, [Part Number]) as LastName_Part_Description
[Part Description],Subfield([Part Description],' ',-1) as [Part Number],
Left Join // Is optional : Yes -> 1 Table, No -> 2 Tables
LOAD [Part Number],