Pivot & Filter - Alternative option

[vijayrc]

Friends,
I have a scenario like this...The input to Transformer is a record which has 4 occurences of a particular set of fields..
e.g, Input
Common Field1;Common Field2;Common Filed3; FieldA;FieldB;FieldA_1;FieldB_1;FieldA_2;FieldB_2;FieldA_3;FieldB_3
Output:
Common Field1;Common Field2;Common Filed3; FieldA;FieldB
Common Field1;Common Field2;Common Filed3; FieldA-1;FieldB-1
Common Field1;Common Field2;Common Filed3; FieldA-2;FieldB-2
Common Field1;Common Field2;Common Filed3; FieldA-3;FieldB-3

Transformer==>Pivot==>Filter [to filter any of the occurence when FieldA_* is zero]
Is there an alternative to get rid of this filter stage and yet get the task accomplished?
Thanks
Vijay

[DSguru2B]

Probably another transformer after the pivot stage to constraint the output maybe

[ray.wurlod]

Don't pass such rows through the first Transformer. Add the four checks to the output link constraint expression, conjoined with AND operators.

[DSguru2B]

Constraint and then pivot. This way you dont need an extra stage. Why didnt I think of that. Probably I was too tired.

[ray.wurlod]

Too rushed, grasshopper.

Take the time to smell the flowers.

Meditate for a while. Ponder the questions.

[DSguru2B]

Your right, I need to slow down. Probably still a kid in datastage

[vijayrc]

Don't pass such rows through the first Transformer. Add the four checks to the output link constraint expression, conjoined with AND operators. ...

Ray
I'm not clear on this. Did you mean to have the four checks on the output constraint of the pivot ?!?!?! or do you mean to have an output link constraint expression on the first transformer to the pivot to be [if FieldA >0 AND FieldA_2 >0 AND FieldA_3>0 AND FieldA_4 > 0]
Anyhow I need the first transformer to do other stuffs besides passing some rows to the pivot.

[DSguru2B]

No, Constraint it in the first transformer itself. You cannot do it in the pivot stage.

[ray.wurlod]

Yes, I meant in the first Transformer stage. And it's perfectly OK that this stage does "other stuff". All my suggestion adds is an additional condition on the output constraint expression - it does not affect anything else you are doing with this Transformer stage.

[vijayrc]

Yes, I meant in the first Transformer stage. And it's perfectly OK that this stage does "other stuff". All my suggestion adds is an additional condition on the output constraint expression - it does ...

Thanks..I'm not getting a way to work out..If you can get me the expression with my above example, may be that would help.

[ray.wurlod]

Code: Select all

FieldA <> 0 And FieldA_1 <> 0 And FieldA_2 <> 0 And FieldA_3 <> 0

And any other constraint that you may already have.

[vijayrc]

Code: Select all

FieldA <> 0 And FieldA_1 <> 0 And FieldA_2 <> 0 And FieldA_3 <> 0

And any other constraint that you may already have. ...

Ray, by doing this, Only if all the four fields [FieldA,FieldA_2,Field_3,Field_4 are non-zeros] then the constraint gets satisfield.
My requirement is if FieldA is 0, FieldA,FieldB shouldn't be written out,
If FieldA_2 > 0, FieldA_2,FieldB_2 should be writtenout and
If FieldA_3 > 0, FieldA_3,FieldB_3 should be writtenout and
If FieldA_3 = 0, FieldA_4,FieldB_4 shouldn't be writtenout

[ray.wurlod]

You did not make that clear till now.
Your only solution will require four separate output links, separately constrained, since they will have different metadata.