Generate Column value

kris_r · Post by **kris_r** » Thu Oct 12, 2006 9:44 am

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

meena · Post by **meena** » Thu Oct 12, 2006 10:24 am

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

kris_r · Post by **kris_r** » Thu Oct 12, 2006 10:42 am

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]

ukyrvd · Post by **ukyrvd** » Thu Oct 12, 2006 11:09 am

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

kris_r · Post by **kris_r** » Thu Oct 12, 2006 11:19 am

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.

ukyrvd · Post by **ukyrvd** » Thu Oct 12, 2006 11:35 am

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

kris_r · Post by **kris_r** » Fri Oct 13, 2006 12:38 am

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

ray.wurlod · Post by **ray.wurlod** » Fri Oct 13, 2006 12:43 am

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.

kris_r · Post by **kris_r** » Fri Oct 13, 2006 1:01 am

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.

ray.wurlod · Post by **ray.wurlod** » Fri Oct 13, 2006 1:28 am

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.

kris_r · Post by **kris_r** » Fri Oct 13, 2006 1:38 am

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

ArndW · Post by **ArndW** » Fri Oct 13, 2006 2:27 am

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

kumar_s · Post by **kumar_s** » Fri Oct 13, 2006 7:03 am

Work out in reverse order. Sort it in desending.
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

Asign val_b to d.

DSXchange

Generate Column value

Generate Column value

Re: Generate Column value