7 records at source and just one at the target

[I_Server_Whale]

Hi All,

I have created a job as shown below:

Code: Select all

SEQ.FILE------->TRANSFORMER-------->SEQ.FILE

The source file has 3 columns and 7 records as shown below:

Code: Select all

STARTTIME, RC, ENDTIME

The target file is supposed to have 9 columns and just one record as shown below:

Code: Select all

STARTTIME,RC1,RC2,RC3,RC4,RC5,RC6,RC7,ENDTIME

The source and target should be mapped the following way:

w.r.t target

Code: Select all

 STARTTIME(target)----->STARTTIME of the 7th record in the source.
RC1---->RC of the 1st record in the source.
RC2---->RC of the 2nd record in the source.
RC3---->RC of the 3rd record in the source.
RC4---->RC of the 4th record in the source.
RC5---->RC of the 5th record in the source.
RC6---->RC of the 6th record in the source.
RC7---->RC of the 7th record in the source.
ENDTIME------>ENDTIME of the 7th record in the source. 

How do I do the mapping in the transformer? Any ideas please!

Thanks much,
Whale

[pnchowdary]

Hi NaveenD,

I would do it in two steps

1) You can use stage variables in the transformer and keep concatenating the RC column into another dummy column.

2) In the next transformer you can take the value in the last row of the dummy column which will contain the concatenated RC value of all the 7 rows and the other columns you need and write them to the target.

[I_Server_Whale]

Hi Naveen,

You mean the job now has two transformers? Is that true?
Please let me know. Thanks a lot man!

Whale

[pnchowdary]

Hi NaveenD,

Yes the job would have to use two additional transformers.

[I_Server_Whale]

Hi Naveen,

Do you mean three transformers in total? Please let me know,

Thanks!
Whale

[pnchowdary]

Hi NaveenD,

To implement the logic, that I suggested, you would require 2 transformers. If you want to implement the first part of the logic in the transformer that you already have in your job, then you would require only 2 transformers total, if not 3.

[I_Server_Whale]

Hi Naveen,

How about using a Pivot Stage for changing the columns into rows? Does that work? Any idea?

Thanks much,
Whale

[I_Server_Whale]

Hi Naveen,

Code: Select all

I would do it in two steps 

1) You can use stage variables in the transformer and keep concatenating the RC column into another dummy column. 

2) In the next transformer you can take the value in the last row of the dummy column which will contain the concatenated RC value of all the 7 rows and the other columns you need and write them to the target. 

What do I assign to the Stage Variable? RC from source?
And then where do I concatenate? How to concat in the derivation?

Thanks a zillion

Whale

[pnchowdary]

Hi NaveenD,

I wasn't able to reply earlier as I was busy with my work. Anyway, here is your solution

1) Declare two Stage Variables and in their derivation column, inlcude the below condition

Code: Select all

Derivation                                 Stage Variable

InLink.InCol                                 NEWVAL

If @INROWNUM =1                              OLDVAL
    Then ""                    
    Else NEWVAL:OLDVAL                 
           

2) In your Dummy column derivation field, include the below condition

Code: Select all

Derivation                                Column Name 

If @INROWNUM =1                             DUMMY
    Then InLink.InCol 
    Else InLink.InCol:OLDVAL     

Let me know if it works for you.

[I_Server_Whale]

Naveen,

I can understand if you are busy. It worked. Thanks a lot.

Thanks again,
Whale

[pnchowdary]

Hi NaveenD,

Good that it worked out for ya :D