Generating ordernumbers from a CustomerReferenceField

[pacoli2013]

Hello,

I have a problem to generate ordernumbers in one table. In a table Customerreference I have one field CustomerReference.

In this field filled by different persons in different ways, ordernumbers are on different places.

An order number is always numerical, always starts with a 6 and is 10 digits long. It can start at position 1 without a prefix. when starting on another position first two digits of the prefix is AB, aB, Ab or ab. But the prefix also can be AB-Nr.: or AB-NR: or AB -, etc

Some examples to make clear what I mean/search and what my problem is

Example 01: AB 94760/AB 6152002585 Berech. gem. Przbyla

Here the ordernumber is 6152002585.

Example 02: AB 6152039896 / RG 6153038808

Here the ordernumber is 6152039896.

Example 03: 4500492086 RG 6153126142 AB 6152122056

Here the ordernumber is 6152122056.

Example 4: 4500598146, AB 6152120780, RG 6153122969

Here the ordernumber is 6152120780.

So I generate a first table with ordernumbers starting at position 1:

Table01:
NoConcatenate Load

CustomerReference                                   as EvaluationNumber01,
       left(CustomerReference, 10)                          as OriginalOrderNumber
Resident ReferenceTable
       where Num(left(CustomerReference, 1)) = 6;

For the second table  I generate a Midfield starting at position 4 and 10 long with filters on the first 2 positions, on the first position of the midfield (=6)

Table02:
NoConcatenate Load
      CustomerReference                               as EvaluationNumber02,
       left(CustomerReference, 2)                    as TestField01,
       (Mid(CustomerReference, 4, 10))             as MidField01
Resident ReferenceTable
       where (left(CustomerReference, 2) = 'AB'
       or left(CustomerReference, 2) = 'Ab'
       or left(CustomerReference, 2) = 'aB'
       or left(CustomerReference, 2) = 'ab' )
       and left(LTrim(Mid(CustomerReference, 3, 11)), 1) = 6
       and len(LTrim(Mid(CustomerReference, 3, 11))) = 10
       ;

I have some garbage in both tables and not filtered all ordernumbers from the main table. I hope some one can help me with this, to create one table with all ordernumber and without

Hello,

I have a problem to generate ordernumbers in one table. In a table Customerreference I have one field CustomerReference.

In this field filled by different persons in different ways, ordernumbers are on different places.

An order number is always numerical, always starts with a 6 and is 10 digits long. It can start at position 1 without a prefix. when starting on another position first two digits of the prefix is AB, aB, Ab or ab. But the prefix also can be AB-Nr.: or AB-NR: or AB -, etc

Some examples to make clear what I mean/search and what my problem is

Example 01: AB 94760/AB 6152002585 Berech. gem. Przbyla

Here the ordernumber is 6152002585.

Example 02: AB 6152039896 / RG 6153038808

Here the ordernumber is 6152039896.

Example 03: 4500492086 RG 6153126142 AB 6152122056

Here the ordernumber is 6152122056.

Example 4: 4500598146, AB 6152120780, RG 6153122969

Here the ordernumber is 6152120780.

So I generate a first table with ordernumbers starting at position 1:

Table01:
NoConcatenate Load

CustomerReference                                   as EvaluationNumber01,
       left(CustomerReference, 10)                          as OriginalOrderNumber
Resident ReferenceTable
       where Num(left(CustomerReference, 1)) = 6;

For the second table  I generate a Midfield starting at position 4 and 10 long with filters on the first 2 positions, on the first position of the midfield (=6)

Table02:
NoConcatenate Load
      CustomerReference                               as EvaluationNumber02,
       left(CustomerReference, 2)                    as TestField01,
       (Mid(CustomerReference, 4, 10))             as MidField01
Resident ReferenceTable
       where (left(CustomerReference, 2) = 'AB'
       or left(CustomerReference, 2) = 'Ab'
       or left(CustomerReference, 2) = 'aB'
       or left(CustomerReference, 2) = 'ab' )
       and left(LTrim(Mid(CustomerReference, 3, 11)), 1) = 6
       and len(LTrim(Mid(CustomerReference, 3, 11))) = 10
       ;

I have some garbage in both tables and not filtered all ordernumbers from the main table. I hope someone can help me with this, to create one table with all ordernumber and without garbage.

I have added a document with my results so far.

Regards in advance

Court

.

I have added a document with my results so far.

Regards in advance

Court

[Saravanan_Desingh · Accepted solution]

One solution is here. You can add more logic like LEN etc..

tab1:
LOAD CustomerReference,
	If(CustomerReference Like '6?????????*',
		If(IsNum(Left(CustomerReference,10)),Left(CustomerReference,10)
		),
		If(CustomerReference Like '*AB 6?????????*', Mid(CustomerReference,Index(CustomerReference, 'AB 6')+3,10)
		)
	) As CustRef
FROM
[I:\CTI\VDIDownloads\CustomerReference.xls]
(biff, embedded labels, table is Sheet1$)
Where CustomerReference Like '6?????????*'
Or CustomerReference Like '*AB 6?????????*'
;