sequential file as target

[sonia jacob]

Hi,

A single row from the target needs to be inserted as two different rows with a column having two different values .

Eg

Row of the Source

Item : Product 1
Type : Vehicle

Item : Product 2
Type : Truck

The 3 rows of the Target for the above input

1.
Item : Product 1
Type : Vehicle

2.
Item : Product 2
Type : Vehicle

3.
Item : Product 2
Type : Truck

The requirement is, for every "non-vehicle" type I need to insert two records one with type as "vehicle" and the other with the source-type itself.
But my target is a sequential file. Currently I am writing onto two different flat / sequential files. Using a transformer i send all the "types" of data to a File1 and all the "non-vehicle" type to File2 with the type hardcoded as "Vehicle". These two files are then mergerd using the MERGE stage to ensure there is no duplication.

Is there any other easy way to do the same?

thanks and regads
Sonia

[sumitgulati]

One way could be:

Using a constraint route all "non-vehicle" type records through a pivot stage where you can break each record into two. One with the original type (say 'Truck') and the other one with type hardcoded to 'vehicle'.

Regards,
Sumit

[sonia jacob]

but the issue is collecting all the records back to a sequential file. the moment i split them i end up overwriting the file. Its not a DB table.

[sumitgulati]

ok. Then you probably can try this.

Pass all the records through a pivot stage and break them into two records. One with the original type and one with type value hardcoded to 'vehicle'. Also add a dummy column in the pivot stage say DUMMY that would store values '1' and '2'.

Input records to Pivot Stage

1.
Item : Product 1
Type : Vehicle

2.
Item : Product 2
Type : Truck

Output from Pivot Stage should be

1.
Item : Product 1
Type : Vehicle
Dumm : 1

2.
Item : Product 1
Type : Vehicle
Dumm : 2

3.
Item : Product 2
Type : Truck
Dummy : 1

4.
Item : Product 2
Type : Vehicle
Dummy : 2

Out of these 4 output records you need to load record 2, 3 and 4.

Now take the output of pivot stage to a transformer where you can negelect all the records that have Type = 'Vehicle' AND Dummy = '1'

The contstraint condition you will have to use is
TYPE <> 'Vehicle' OR DUMMY <> '1'

The output of the transformer can be directly fed to your target sequential file.

Code: Select all

 SOURCE --> PIVOT --> TRANSFORMER --> TARGET

Using this logic you are not splitting the records anywhere. Hope this should work.

Regards
-Sumit

[vmcburney]

Using your original job design send both your output streams to a link collector stage that will collect them into a single stream that can be written out to one sequential file.

[sumitgulati]

Yaa, link collector is also an option but make sure you set Inter Process Row buffering in the job properties.

-Sumit

[sonia jacob]

thanks guys

[roy]

Hi,
This sounds like a clasic case for the old CRLF or LF trick.
since your writing to a seq file embedding Char(13) : Char(10) in windows or Char(10) in unix with in your derivation row will cause a new line in your file hence writing more then 1 row to the seq file.
you can produce the output line in a stage variable or write a basic routine that returns the entire row with embeded new lines in it.
(there are several posts in this forum with this info)

i.e.
lets say FD is your seq file column delimiter (for coma FD = ",")
you can use a Stage Variable to build your Vehical line if needed

Code: Select all

SVAddedLine = If InputLink.Type <> 'Vehical' Then InputLink.Item : FD : 'Vehical' : Char(13) : char(10) Else ""
Derivation Column = SVAddedLine : InputLink.Item : FD : InputLink.Type

(the Else in the SV is an empty string)

IHTH,