Splitting a field

[#1]

I am pulling data from SAP and in my File, the field OBJNR, is the key, but I need to split this into 4 separate fields.

Position 1-2 = Field 1

Position 3 - 6 = Field 2

Position 7 16 = Field 3

Position 16 - 22 = Field 4.

I only need Field 2 and Field 3.

I was using Left and Right

Left(OBJNR,6) as FIELD1

Right(FIELD1,4) as FIELD2

When I try and run this I get an error on the Right Statement that FIELD1 does not exist. 

I can seen that my data is in FIELD1, but I then need to strip out the first two positions cause I do not need them.

I tried subfield, but did not have luck with that either.

[fred_s]

Hi Diane,

Share your file with some (example) data.

I'm sure you'll get the answer in a couple of minutes..

Fred

[ramoncova06]

since the load is reading from the current table,  'FIELD1' does not exist because you are creating it on you current load

try with this method

Right(Left(OBJNR,6),4)  as FIELD2

you could also try with peek but that is just adding complexity

[Anonymous]

FIELD1 cannot be referenced in the same load statement as it does yet exist as an input.

Use the Mid() function four times on OBJNR to create your 4 required fields.

[Anonymous · Accepted solution]

Use mid() function:

mid(OBJNR,3,4) as Field2

mid(OBJNR,7,10) as Field3