difference between two rows

adams06 · Post by **adams06** » Fri Dec 14, 2012 1:22 pm

How to get difference between two rows for a column field?

ex:

rowInt  Value
99       0
98       1
97      10
96       0

chulett · Post by **chulett** » Fri Dec 14, 2012 1:52 pm

That's an example of what - your input data? If so, what would your desired output data look like?

adams06 · Post by **adams06** » Fri Dec 14, 2012 2:12 pm

thats the input

rowInt Value
97 9

chulett · Post by **chulett** » Fri Dec 14, 2012 2:19 pm

Sorry but we'll need more detail than that to provide a legitimate answer. What are your rules for computing this "difference"?

adams06 · Post by **adams06** » Fri Dec 14, 2012 2:27 pm

I am in the process of reading sqlldr log file

skipped 0
read 10
rejected 1
discarded 0

so i need to compute the records loaded. Thats the background :D

chulett · Post by **chulett** » Fri Dec 14, 2012 3:21 pm

Good to know, should have put that in your original post. So... you want:

read - rejected - discarded?

For that I would just use the aggregator. For the last two rows, multiply the number by -1 before sending it to aggregation and then sum() them. What about the skipped value if it is non-zero? You may need to not send it to the aggregator at all, if that's the case just use a constraint.

ray.wurlod · Post by **ray.wurlod** » Fri Dec 14, 2012 3:24 pm

Otherwise use stage variables in a Transformer stage to "remember" the value from the previous row, a technique that has been explained more than once on DSXchange.

adams06 · Post by **adams06** » Wed Dec 19, 2012 2:35 pm

Chulett your suggestion works for me thanks for the help.