DSXchange

Posted: **Thu Jul 28, 2005 9:58 am**

Hi All,

I have created a job as shown below:

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

Posted: **Thu Jul 28, 2005 10:10 am**

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.

Posted: **Thu Jul 28, 2005 10:22 am**

Hi Naveen,

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

Whale

Posted: **Thu Jul 28, 2005 10:24 am**

Hi NaveenD,

Yes the job would have to use two additional transformers.

Posted: **Thu Jul 28, 2005 10:30 am**

Hi Naveen,

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

Thanks!
Whale

Posted: **Thu Jul 28, 2005 10:41 am**

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.

Posted: **Thu Jul 28, 2005 11:07 am**

Hi Naveen,

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

Thanks much,
Whale

Posted: **Thu Jul 28, 2005 1:40 pm**

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

Posted: **Thu Jul 28, 2005 4:53 pm**

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.

Posted: **Fri Jul 29, 2005 11:40 am**

Naveen,

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

Thanks again,
Whale

Posted: **Fri Jul 29, 2005 2:10 pm**

Hi NaveenD,

Good that it worked out for ya :D

DSXchange

7 records at source and just one at the target

7 records at source and just one at the target