DSXchange

I'm getting following value of derivation in column flag_value from a table

if ((currentMonth <= 3 and ( ((mbrSubPlnAdjEndDt ="") or ((mbrEnrEndDtMonth >= 1) and (mbrEnrEndDtYear >= (currentYear - 1)))))) or (currentMonth > 3 and ( ((mbrSubPlnAdjEndDt ="") or ((mbrEnrEndDtMonth >= 7) and (mbrEnrEndDtYear >= currentYear)))))) then 'A' else 'I'

I'm calculating stage variables CurrentMonth, mbrSubPlnAdjEndDt and others in Stage Variable and above derivation I'm getting in other source column. This derivation is different for different Clients so it is going to be dynamically set. Here problem is when I'm passing this derivation in output column flag_value in derivation in transformer, it is taking it as text and not from stage variables calculated and not doing calculation of if/else. Could anyone please help how can we make transformer to take it as expression instead of text and do calculation for each record?

The transformer stage doesn't support dynamic derivation expressions. The design-time derivation expression is compiled into the executable code.

If you have a reasonable number of clients that won't change often, you could handle the variable logic with a bunch of nested IF-THEN-ELSE.

If you want more flexibility, you will have to look at other design alternatives (e.g. custom routine).

Mike

Hi Mike, thanks but please advise "bunch of nested if else" what do u mean?

can you add a table to your database so you could look it up in whole or part? For example if each client has a static piece and the rest of the derivation is more consistent?

Bunch of nested if-then-else:

If Client = "A" Then Client A logic
Else If Client = "B" Then Client B logic
Else If Client = "C" Then Client C logic
...

Not at all flexible since you will have to change the job every time you add a client or a client needs a derivation change.

You'll have to go in another direction if flexibility is important to you.

Mike

Yes, Thanks Mike, I'm creating Temp Table in DB with those variables as column and then doing custom derivation in SQL and it is working.