field function
Moderators: chulett, rschirm, roy
field function
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
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
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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
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
-
- Charter Member
- Posts: 6
- Joined: Thu Mar 27, 2003 1:55 pm
- Contact:
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.
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.
Mark Ott
DataStage Architect
Adept Technologies, Inc.
DataStage Architect
Adept Technologies, Inc.
See if the following custom routine helps you.
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.
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Now THAT Would be perfect ..! :Dray.wurlod wrote:So there appears to be a need for a PEPC (predict every possible contingency) stage?
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.
~The simpliest solutions are always the best~
~Trick is to understand the complexity to implement simplicity~
~Trick is to understand the complexity to implement simplicity~