Generate Column value
Moderators: chulett, rschirm, roy
Generate Column value
I have a scenario like this..
The column d should be generated based on the logic below.
a b c d
1 10 2 11
1 11 2 12
1 11 2 12
1 11 2 12
1 12 2 15
1 15 2 NULL
a and c are my keys which will have same value.
The value of the second column in b should be populated as first value of d.
The next change after 11 in b is 12 which should be poplated in second place in d.
if the values repeat in b, the same values should be populated in d till the next change appears in b.
Could anyone please guide me through this...
The column d should be generated based on the logic below.
a b c d
1 10 2 11
1 11 2 12
1 11 2 12
1 11 2 12
1 12 2 15
1 15 2 NULL
a and c are my keys which will have same value.
The value of the second column in b should be populated as first value of d.
The next change after 11 in b is 12 which should be poplated in second place in d.
if the values repeat in b, the same values should be populated in d till the next change appears in b.
Could anyone please guide me through this...
Re: Generate Column value
Hi,
The logic looks confusing by seeing your data. Be clear what exactly you want to do at column 'd'.
The logic looks confusing by seeing your data. Be clear what exactly you want to do at column 'd'.
I have a scenario like this..
The column d should be generated based on the logic below.
a b c d
1 10 2 11
1 11 2 12
1 11 2 12
1 11 2 12
1 12 2 15
1 15 2 NULL
a and c are my keys which will have same value.
The value of the second column in b should be populated as first value of d.
The next change after 11 in b is 12 which should be poplated in second place in d.
if the values repeat in b, the same values should be populated in d till the next change appears in b.
Could anyone please guide me through this...
The column D values have to be populated from column b.
The second value of b should be the first value of column d.
If the value of b is repeating, the same value should be generated in b till we get the next change.
a b c d
1 10 2 11
1 11 2 12
1 11 2 12
1 11 2 12
1 12 2 15
1 15 2 NULL
For Ex: second value of b i.e 11 --> First value of d.
Next value changed in b is 12.
So, 12 should be populated in d till the next change.
When we get 15 in B, which is the next change after 12,
it should be populated in d.
Hope this clarifies to an extent.[/code]
The second value of b should be the first value of column d.
If the value of b is repeating, the same value should be generated in b till we get the next change.
a b c d
1 10 2 11
1 11 2 12
1 11 2 12
1 11 2 12
1 12 2 15
1 15 2 NULL
For Ex: second value of b i.e 11 --> First value of d.
Next value changed in b is 12.
So, 12 should be populated in d till the next change.
When we get 15 in B, which is the next change after 12,
it should be populated in d.
Hope this clarifies to an extent.[/code]
what is the data type of B?? is the length of B alwase fixed??
anyway this is what I would do ..
stage variables: sv_1, sv_2, sv_3
sv_1 (counter) if not initialized yet initialize sv_1 else compare with sv_2 if they are different update counter else have same value
sv_2 = B
sv_3= first char of B : sv_1
Use sv_3 as the output columns derivation for D
anyway this is what I would do ..
stage variables: sv_1, sv_2, sv_3
sv_1 (counter) if not initialized yet initialize sv_1 else compare with sv_2 if they are different update counter else have same value
sv_2 = B
sv_3= first char of B : sv_1
Use sv_3 as the output columns derivation for D
thank you
- prasad
- prasad
>>The column b need not be generated
Above logic will not generate B ..
you can TRY this:
stage variables: sv_2old, sv_counter, sv_d, sv_2new in same order
sv_2old = sv_2new
sv_counter = update if sv_2old and sv_2new are different.
sv_d = derive depending upon requirement (first char from B and sv_counter)
sv_2new= B
Use sv_d as the output columns derivation for D
Above logic will not generate B ..
you can TRY this:
stage variables: sv_2old, sv_counter, sv_d, sv_2new in same order
sv_2old = sv_2new
sv_counter = update if sv_2old and sv_2new are different.
sv_d = derive depending upon requirement (first char from B and sv_counter)
sv_2new= B
Use sv_d as the output columns derivation for D
thank you
- prasad
- prasad
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
There is no lookahead logic possible unless you use two Transformer stages. You do the stage variable things in the first Transformer stage, pass all the results plus original source data to the downstream Transformer stage, and effect the comparison and calculations therein.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Then your logic needs work.
Try creating a specification in English of how column D is to be derived in English. This is often useful as a mind-clearing, or at least mind-focussing, exercise. Post the specification here and we may be able to target assistance more accurately.
Try creating a specification in English of how column D is to be derived in English. This is often useful as a mind-clearing, or at least mind-focussing, exercise. Post the specification here and we may be able to target assistance more accurately.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Kris_r - become a premium member and you will see that message and many more - and also get more detailed responses to your queries.
Last edited by ArndW on Fri Oct 13, 2006 7:48 am, edited 1 time in total.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Work out in reverse order. Sort it in desending.
You can do this using some stage variable in transformer.
Something like this,
Asign val_b to d.
You can do this using some stage variable in transformer.
Something like this,
Code: Select all
Initialize variable
Prev_b = ''
val_b = NULL
val_b = If Prev_b = Link.b Then val_b Else Prev_b
Prev_b = Link.b
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'