Gareth Horton
July 12th, 2002, 02:53 PM
Darrel Shoji wrote:
> I have a report that is formatted roughly as follows:
>
> Product/Material Material Cost
> E123456789 4.950 R
> 1.060 P
>
> D22221100 3.980
> D11112200 1.650
>
>
>
> Where:
> R=Raw materials
> P=Packaging materials
>
> Not all of the report lines have this split of costs. How do I pick up the lines that have this split and still pick up the single line details? I am trapping detail lines based upon the Product Material having a alpha in position 1 and numerics in positions 4-7
>
Darrel,
You will have to use the Advanced Field Properties to trap both lines (when they appear)
and then split the values up using a calculated field in the table.
1. Create the trap (single line) for the data and paint the necessary fields, making sure that the field
that contains the split will catch both possible entries. (You may be able to split out the "R" and "P" from
the numeric data if you are lucky - it depends on the formatting in your source report - but for now, lets just say we have to grab the whole lot together)
2. Doubleclick on the costs field, go into the advanced tab and choose "End Field On:" - Blank Field Values 1.
(you may have to experiment with these settings, depending on the rest of the data)
3. When you preview, you now see that both costs lines are trapped.
4. In the table, your costs field (should be a character field at this stage) now contains something like:
4.950 R 1.060 P
5. We probably should LTrim first, to remove any leading spaces, then start breaking the string apart in the calculated fields we need, then using a VAL, to get back to a number.
6. Here we would use an LSplit, using " " (a space) as the split character.
7. First calculated field will be called CostRaw - essentially the first Costs (R) field.
Note: This does apply to the way I have trapped, so you may need to adapt this.
Val(LSplit((LTrim(Costs)),5," ",1))
This essentially trims the Costs field of leading spaces, then breaks up the string into 5
pieces separated by spaces and returns the first pice of data. Then the VAL function brings it
back to a numeric type.
8. Second calculated field called CostPack
Val(LSplit((LTrim(Costs)),5," ",4)) - This takes the 4th piece - This is the (P) costs field.
This leaves you with something like this in the table (the costs field can be hidden out of the
way if you wish)
NUMBER COSTS COSTRAW COSTPACK
E123456789 4.950 R 1.060 4.950 1.060
D22221100 3.980 3.980
D11112200 1.650 1.650
I hope this is what you were looking for
Gareth Horton
Datawatch
> I have a report that is formatted roughly as follows:
>
> Product/Material Material Cost
> E123456789 4.950 R
> 1.060 P
>
> D22221100 3.980
> D11112200 1.650
>
>
>
> Where:
> R=Raw materials
> P=Packaging materials
>
> Not all of the report lines have this split of costs. How do I pick up the lines that have this split and still pick up the single line details? I am trapping detail lines based upon the Product Material having a alpha in position 1 and numerics in positions 4-7
>
Darrel,
You will have to use the Advanced Field Properties to trap both lines (when they appear)
and then split the values up using a calculated field in the table.
1. Create the trap (single line) for the data and paint the necessary fields, making sure that the field
that contains the split will catch both possible entries. (You may be able to split out the "R" and "P" from
the numeric data if you are lucky - it depends on the formatting in your source report - but for now, lets just say we have to grab the whole lot together)
2. Doubleclick on the costs field, go into the advanced tab and choose "End Field On:" - Blank Field Values 1.
(you may have to experiment with these settings, depending on the rest of the data)
3. When you preview, you now see that both costs lines are trapped.
4. In the table, your costs field (should be a character field at this stage) now contains something like:
4.950 R 1.060 P
5. We probably should LTrim first, to remove any leading spaces, then start breaking the string apart in the calculated fields we need, then using a VAL, to get back to a number.
6. Here we would use an LSplit, using " " (a space) as the split character.
7. First calculated field will be called CostRaw - essentially the first Costs (R) field.
Note: This does apply to the way I have trapped, so you may need to adapt this.
Val(LSplit((LTrim(Costs)),5," ",1))
This essentially trims the Costs field of leading spaces, then breaks up the string into 5
pieces separated by spaces and returns the first pice of data. Then the VAL function brings it
back to a numeric type.
8. Second calculated field called CostPack
Val(LSplit((LTrim(Costs)),5," ",4)) - This takes the 4th piece - This is the (P) costs field.
This leaves you with something like this in the table (the costs field can be hidden out of the
way if you wish)
NUMBER COSTS COSTRAW COSTPACK
E123456789 4.950 R 1.060 4.950 1.060
D22221100 3.980 3.980
D11112200 1.650 1.650
I hope this is what you were looking for
Gareth Horton
Datawatch