previous record to output file

[hemaarvind1]

Hi team,

I have a requirement as below: could you please help me in this:


we have a file which has the records coming in where the file has the source fields as well as the fields retreived from a lookup.For easier identification, I mention the set of source fields as S and set of retreived lookup fields as W.
The transformation is ,assume you have two columns col1 and col2 coming from S(direct source records).

If (previous col1 = current col1& previous col2= current col2)

Add +1 to COUNT


Move W fields (records retrieved from lookup) until COUNT = 18, ignore the records greater than 18


If ( current-col1 not equal to previous- col1) or
(current-col1 equal to previous-col1 and current-col2 not equal to previous-col2)

Write the previous record to the output file along with the COUNT

COUNT = 1

Move the S and W fields of the current record.

[rameshrr3]

Stage Variables in a transformer stage can store values of the previous record. Create a counter . Make sure teh input to the transformer stage is correctly has partitioned and sorted on the columns that you will compare with .

I have done something similar using a server job ,and i see no reason why it cannot be done with a parallel job . No extra functions or routines required.

[hemaarvind1]

Thank you for your input.However, my requirement is to consider the whole input row and compare with previous row. I have done scenario where I pick up few fields(previous and current) and do transformations based on previous ,however,
picking up a whole record into a transformer is something I didn't do before.

Could you please suggest how to pick up the whole input row into a stage variable.

[chulett]

It can be as simple as concatenating all of the field values into a single stage variable much like you were going to compute a checksum. In 8.5+ there are two functions that automate that: SaveInputRecord() and GetSavedInputRecord().

[hemaarvind1]

Thank you the suggestions. I may not be clear in the requirement.

I accept that we can get all the fields into a stage variable by using concat .However, I wanted to get these fields into the output as field by field instead of a single stage variable getting into one field. Means,we need to get the record into the output as if we are loading a whole record field by field.

Hope this helps!

[bhasds]

Hi Hemaarvind1,

You can comapare the entire record with two stage variable.
Eg- if you have col1 and col2 coming from the source

col1:"|":col2 SV1
If then else condition SV2
SV1 SV3

[bhasds]

we need to get the record into the output as if we are loading a whole record field by field

The above is possible using field function for each column derivation

Code: Select all

Eg-
  Field(SV1,"|",1)  col1
  Field(SV1,"|",2)    col2

[chulett]

I may not be clear in the requirement.

That much is true.

I did say it can be as simple as concatenating, not that was the only or preferred way. Not clearly knowing what you are trying to accomplish means you may not get the most cogent reply. You could easily build the stage variable to look just like your output record by adding the delimiters it would need and then writing it out as a single large string which would fulfill your "as if" requirement. You could also look at the Column Export folowed by the Column Import stages to do that work for you or as bhasds posted you could 'manually' reparse the stage variable back into its component fields.

You never mentioned your DataStage version, as noted there are new functions available that could help here.