Autofill Column Until Next Value
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 12
- Joined: Thu Jun 29, 2017 9:48 am
- Location: Washington DC
Autofill Column Until Next Value
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
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
-
- Participant
- Posts: 12
- Joined: Thu Jun 29, 2017 9:48 am
- Location: Washington DC
-
- Participant
- Posts: 12
- Joined: Thu Jun 29, 2017 9:48 am
- Location: Washington DC
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.
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:
Then simply assign svVoiceUsage to the output column.
Code: Select all
svVoiceUsage: IF <value should change> THEN <compute new value> ELSE svVoiceUsage
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 12
- Joined: Thu Jun 29, 2017 9:48 am
- Location: Washington DC
-
- Participant
- Posts: 12
- Joined: Thu Jun 29, 2017 9:48 am
- Location: Washington DC
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.
If it would help, you can upload screenshots to any of the free image sharing sites out there and link to them here.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 12
- Joined: Thu Jun 29, 2017 9:48 am
- Location: Washington DC
-
- Participant
- Posts: 12
- Joined: Thu Jun 29, 2017 9:48 am
- Location: Washington DC
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
Input file:
https://ibb.co/nyGh9a
Datastage Job:
https://ibb.co/dBFLvF
Transformer Stage:
https://ibb.co/kSDjNv
End Result:
https://ibb.co/fOC0vF
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 12
- Joined: Thu Jun 29, 2017 9:48 am
- Location: Washington DC