DSXchange

Posted: **Wed May 09, 2007 8:34 am**

HI,

one of columns values from seq file look like this

SolditemYear
CAR 3 (3 spaces between 2 fileds)
CAR 50 (4 spaces between 2 fileds)
MD - 71(No space)
ASSO 82(No Space)
3 YE 95(No space)

I need to split the item and year into 2 separate fields
I used 2 stage variables

FIELD(LkInXfm.SOLDITEMYEAR," ",1)
FIELD(LkInXfm.SOLDITEMYEAR," ",2)

it can split correctly only 'ASSO 82'

I want to split remaing values also like
car 3
car 50
MD - 71
3 YE 95

can you please help me how to do ?
few records has two sapces and few records has 3 spaces.Actually there is no consistencey.

I think client entered manully these values

Thanks

Posted: **Wed May 09, 2007 8:44 am**

In the field "CAR 3", is 3 the year? Or is it supposed to be 03 and you did not include the 0. Where I am getting to is that if the last two places are always going to be the year then you can use Right(in.Link, 2) for the year part and LEFT(in.Link, LEN(in.Link)-2) for the Type.

Posted: **Wed May 09, 2007 8:57 am**

Thanks for your reply DSGuru

Data looks exactly like this

CAR 3 (3 spaces between 2 fileds)
CAR 50 (4 spaces between 2 fileds)
MD - 71(No space) ' here I need to put 'MD -' in one filed and 71 in another filed
ASSO 82(No Space)
3 YE 95(No space)

Thanks

Posted: **Wed May 09, 2007 9:17 am**

I forgot to mention in my previous mail,few records have
just type , no year mentioned.

like

MAT
NAT no year mentioned here

when I use

LEFT(LkInXfm.SOLDYEAR, LEN(LkInXfm.DEGRYEAR)-2)
right(LkInXfm.SOLDYEAR,2)

when I use these functions , it is splitting previus record like M AT
can you please suggest me what needs to be done

Posted: **Wed May 09, 2007 9:24 am**

It appears that your source data can have several different formats but basically representing two separate fields. I'd suggest that you create a simple DS routine that uses the MATCH[ES] command in a case structure.

If the extraneous spaces are not required for the output then I'd first Trim the string and then use the following logic:

begin case
*** 3X2N stands for 3 char and 2 numbers ***
case string matches '3X2N'
field1 = string[1,3] or Left(string,1,3)
field2 = string[4,2] or Right(string,4,2)
*** 4X1N stands for 4 char and 1 number ***
case string matches '4X1N'
field1 = ...
field2 = ...
case ...
end case

Ans = field1:"|":field2

Then you can parse the returned value based on the "|" character within the target column's derivation or within stage variables. This logic is not fool proof but can be customized to handle your non-standard data formats.

Posted: **Wed May 09, 2007 9:25 am**

See if the following custom routine helps you.

Code: Select all

      FUNCTION SplitRecord(Arg1)
      FieldToSplit = TRIM(Arg1)
      Type = ""
      Year = ""
      tmpType = ""
      tmpYear = ""
      FLAG = @TRUE

      FieldLength = Len(FieldToSplit)

      FOR n = FieldLength To 1 STEP -1
         asciiCode = SEQ(FieldToSplit[n,1])
         IF (asciiCode >= 48 AND asciiCode <= 57 AND FLAG)
         THEN
            tmpYear = FieldToSplit[n,1]
            Year = tmpYear:Year
         END
         ELSE
            tmpType = FieldToSplit[n,1]
            Type = tmpType:Type
            FLAG = @FALSE
         END
      NEXT n

      Ans = Type:"^":Year

Run this in the stage variable. THe values for Type and Yearwill be split by a ^. For 'Type' do FIELD(STGVar, "^", 1) and for 'Year' do FIELD(STGVar, "^", 2).

I chose ^ as a delimiter as its unlikely to show up in the data. If your data consists of this field then use a delimiter which will not show up.

Go through the code and understand it well. Do through testing.

Posted: **Wed May 09, 2007 9:40 am**

Thanks MATT and DSGuru for your suggestions.
let me take a look and get the clear picture of the code you send.
Just now I put these 2 derivations in stage variables.I checked for few records , It looks fine.but I am not sure this derivation works if I get different data formats in future.

if alpha(LkInXfm.DEGRYEAR) then LkInXfm.DEGRYEAR else LEFT(LkInXfm.DEGRYEAR, LEN(LkInXfm.DEGRYEAR)-2)

if alpha(LkInXfm.DEGRYEAR) then '' else right(LkInXfm.DEGRYEAR,2)

Thanks

Posted: **Wed May 09, 2007 4:45 pm**

So there appears to be a need for a PEPC (predict every possible contingency) stage?

Posted: **Thu May 10, 2007 1:14 am**

ray.wurlod wrote:So there appears to be a need for a PEPC (predict every possible contingency) stage?

Now THAT Would be perfect ..! :D
That way there will be no need for developers.. hey sri75?

sri75: You cannot predict the future. What I suggest is place a transformation with rejects on and push all exceptions to a file and cater for the different data as it comes in.

DSXchange

field function

field function