Autofill Column Until Next Value

phipsijuice · Post by **phipsijuice** » Thu Jun 29, 2017 10:41 am

Hi! Below is some phone record sample data. What stage would I use to autofill down the "Voice Usage" number into the "Target Number" column until the next "Voice Usage" number? What Stage/function/derivation would I use to accomplish this? I always use a Parallel job and import the data using the "Unstructured" file since I am processing excel spreadsheets. Any help would be appreciated.

Voice Usage For: (555)111-2222
TargetNumber Item ConnDate ConnTime SeizureTime Duration OrigNumber TermNumber
1 10/02/15 00:00:19 0:22 0:05 5551112222 5553335555
2 10/02/15 00:40:18 0:32 0:03 5552223333 5553335555
3 10/03/15 21:26:32 0:33 0:31 5552223333 5553335555
4 10/03/15 22:42:20 0:30 0:59 5552223333 5553335555
5 10/04/15 16:54:19 0:32 0:06 5552223333 5553335555
6 10/07/15 13:55:06 0:27 9:27 5552223333 5553335555
7 10/11/15 00:06:26 0:33 0:04 5552223333 5553335555
8 10/12/15 16:02:08 0:30 0:06 5552223333 5553335555
9 10/12/15 19:20:42 0:30 0:06 5552223333 5553335555
Voice Usage For: (555)333-4444
1 10/20/15 14:00:47 0:21 1:21 5556667777 5557772222
2 10/20/15 21:42:17 0:31 0:19 5556667777 5557772222
3 10/24/15 21:43:13 0:30 0:45 5556667777 5557772222
4 11/05/15 17:19:52 0:30 0:03 5556667777 5557772222
5 11/06/15 01:49:30 0:30 0:07 5556667777 5557772222
6 11/06/15 02:33:47 0:30 0:45 5556667777 5557772222
7 11/07/15 00:59:45 0:31 0:04 5556667777 5557772222
8 11/07/15 17:32:25 0:33 0:03 5556667777 5557772222
9 11/09/15 03:18:03 0:24 3:13 5556667777 5557772222
Voice Usage For: (555)222-3333
1 11/10/15 02:19:21 0:27 2:26 5556669999 5554443333
2 11/11/15 03:19:27 0:01 0:00 5556669999 5554443333
3 11/11/15 03:20:05 0:31 0:03 5556669999 5554443333
4 11/11/15 18:38:27 0:20 6:28 5556669999 5554443333
5 11/12/15 13:26:55 0:22 0:25 5556669999 5554443333
6 11/12/15 17:14:53 0:19 2:04 5556669999 5554443333
7 11/12/15 17:17:23 0:22 4:47 5556669999 5554443333
8 11/13/15 21:34:26 0:29 0:00 5556669999 5554443333
9 11/18/15 14:57:52 0:26 0:50 5556669999 5554443333

chulett · Post by **chulett** » Thu Jun 29, 2017 10:46 am

At a high level: a transformer and a stage variable to capture the number and then to assign it to all of the following records until the next one shows up.

phipsijuice · Post by **phipsijuice** » Thu Jun 29, 2017 11:09 am

Thanks for the reply, but what function/parameter would I use to assign the "Voice Usage" number to all of the following records until the next one shows up?

chulett · Post by **chulett** » Thu Jun 29, 2017 11:48 am

You create the "new" column in the output link of the transformer and assign the value of the stage variable to it as its derivation. Does that help?

phipsijuice · Post by **phipsijuice** » Fri Jun 30, 2017 8:17 am

So this Derivation "If Index(DSLink3.Item,"Voice",1)Then Right(DSLink3.Item, 13) Else DSLink3.TargetNumber" in my Stage Variable which I named "VoiceUsage" in the Transformer Stage will populate the "Voice Usage" number into the "Target Number" column, but what do I need to add to the derivation so that the rows below will be populated with the same "Voice Usage" number until the next value? I appreciate your help with this.

chulett · Post by **chulett** » Fri Jun 30, 2017 9:02 am

In order to preserve the value of a stage variable row over row when it shouldn't change, you set it to itself. Typically that would be incorporated into your "change detection" logic as something like this:

Code: Select all

svVoiceUsage: IF <value should change> THEN <compute new value> ELSE svVoiceUsage

Then simply assign svVoiceUsage to the output column.

phipsijuice · Post by **phipsijuice** » Fri Jun 30, 2017 10:08 am

Ok, we are almost there. Your code works, however how to do I get Datastage to process my file row by row in it's original order? That needs to happen in order for each row in the "TargetNumber" column to be assigned the proper "Voice Usage" number. Thanks!

chulett · Post by **chulett** » Fri Jun 30, 2017 12:04 pm

Probably best to run the job on a single node to accomplish that.

chulett · Post by **chulett** » Mon Jul 03, 2017 3:16 pm

Did you get this worked out?

phipsijuice · Post by **phipsijuice** » Wed Jul 05, 2017 6:07 am

Good morning. I got close, but was not able to get it to work. Would I be able to send you a small file so you could try it on your end and let me know the how you did it? Thanks.

chulett · Post by **chulett** » Wed Jul 05, 2017 6:11 am

Unfortunately, no. I've been an Informatica developer for the last seven years and have no access to DataStage. Someone else may be able to help in that fashion or provide some advice I may have missed. Or you could try explaining just how close you got.

If it would help, you can upload screenshots to any of the free image sharing sites out there and link to them here.

phipsijuice · Post by **phipsijuice** » Wed Jul 05, 2017 10:03 am

I'll post some screenshots of my job and the transformer derivation.

phipsijuice · Post by **phipsijuice** » Wed Jul 05, 2017 12:38 pm

Below is my input file, datastage job, trasnformer stage with Stage Variable and Derivation and the end result when the data is processed through datastage and imported into a table in my Oracle database. Obviously my Stage Variable is not updating the "TargetNumber" column the way I would like.

Input file:

https://ibb.co/nyGh9a

Datastage Job:

https://ibb.co/dBFLvF

Transformer Stage:

https://ibb.co/kSDjNv

End Result:

https://ibb.co/fOC0vF

chulett · Post by **chulett** » Wed Jul 05, 2017 6:54 pm

Okay, that site was blocked where I work so finally got a chance to look at the images you uploaded. First question: I assume your target table should not include those "Voice" lines and the column header names record, yes? Do you have anything for a constraint on the outgoing transformer link?

phipsijuice · Post by **phipsijuice** » Thu Jul 06, 2017 5:40 am

That is correct the "Voice" lines should not be there. My plan was to add a filter stage or another transformer stage to do the cleanup work and remove any unwanted rows. I have no constraints on the outgoing transformer link.