To retain only desired data rows after a Pivot

[sumitgulati]

Are you facing any difficulty in connecting the ouput of the pivot stage to a transformer stage.

Regards,
Sumit

[vmcburney]

I may be missing something obvious but it looks quite simple, wouldn't you have a transformer before and after your pivot stage, use the after pivot transform to remove rows with zero amounts.

You could also use a very complex Oracle SQL statement to do the pivot for you, less desirable then doing it DataStage as it can be harder to maintain and puts load on the database. Does Oracle have a crosstab command?

[RC99]

Are you facing any difficulty in connecting the ouput of the pivot stage to a transformer stage.

Regards,
Sumit

Thanks for reply

I finally found the pdf on Pivot (was iun a different place on drive than per instructions) and set up the pivot
Since no way to access column name in DS, I ended up hardcoding in the values for a descrip field for the amts in separarte fileds per amt before the pivot, put in a second field in the pivot and got out 12 rows, one for each amount column per input row, from the pivot - as needed.
Originally I was looking to hard code in the pivot - which you can't do.

Also sucessfully hooked pivot output stream up to a transform - no problem - someone on a forum had said you need to output to a file - not the case.

now need to establish an icrement field to store number of rows per key (from the pivot) while also deleting rows w zero amts.
any Ideas on how to hold the key while incrementing, but getting rid of zero amts?
there could be several w zero , say for one Key - row 1 and 2 have am,ts, then row # 3 through 11 for that Key have zero amt, and then the 12th would have an amt, so you'd wanr row 1,2,12 with amounts and the accumulator would read = 3.
thanks

[chulett]

Hold it in a Stage Variable and only increment it under the same conditions that you use to pass out a row.

[RC99]

Hold it in a Stage Variable and only increment it under the same conditions that you use to pass out a row.

OK
here's what I have
12 rows coming from the pivot w the same key
to get rid of rows w nonkeyamt = 0, keep rows w nonkeyamt >0
and also add an incrementor to the new compound key (key,incrementor)
I could use a first transform to get rid of zero amt fileds, then a second would do the incrementing per key .
thought it might be better to try it in one transform
am new to stage variables
I set up a stage variable Key - linked input from input link
a second variable PrevKey
a third ExpItemSeq - that would get put in the output column derivation.

how then would you check if Prevkey = incoming Key, also get rid of rows w zero amts, and also inrement the counter for just that key and putting the incrementor into it's output column, and setting intrementor to 0 when it's a new Key?
i tired a derivation that didn't work :
If PrevKey = Key then ExpItemSeq = ExpItemSeq+ 1 else PrevKey = Key and ExpItemSeq = 1
didn't work
is it possible to do this? ie - to increment while also relying on the outlink constraint to bypass rows w 0 amts?
thanks ahead of time

[GavMagill]

I'm not sure if this will be any help as I have not used it before but there is a routine in the Routines/SDK/RowProc category called "RowProcCompareWithPreviousValue".

It looks like it will allow you to check the change of a key value as each row is read in and based on the output from this routine you could reset your counter.

Good luck.
Gavin