DSXchange

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...

Hi,
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]

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

The data type of b is Integer.
The column b need not be generated.
only column d has to be generated whise datatype is also integer.

>>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

Hey,
I tried this logic..
but it did not work...

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.

Hi,
I am implementing this logic in two transformers, all the stage variables in one transformer and the source columns in one trn stage but,
the logic is not fetching me the results. I am able to retreive the next value of column B, but not the set logic until there is a change in B.

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.

Can someone copy and paste the message for me..
I am not able to see the complete message..

Kris_r - become a premium member and you will see that message and many more - and also get more detailed responses to your queries.

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.