Filling in Row Gaps

[cyclogenisis]

Hi,

Looking for help I believe this would fall under the category of a transformer last row in group scenario. But, would like some advice on how to approach - specific details are nice.

I have data somewhat like this

A, 1, z
A, 3, z
A, 4, z
A, 7, z
A, 10, z
B, 4, z
B, 5, z
B, 6, z
B, 10, z

Required Output (basically missing rows):

A, 2, z
A, 5, z
A, 6, z
A, 8, z
A, 9, z
B, 1, z
B, 2, z
B, 3, z
B, 7, z
B, 8, z
B, 9, z

The idea is any rows that weren't found with a 2nd column value between 1 and 10 were created in the output, the key sort/group column being col1. Let me know if you need me to go into more detail.

[chulett]

So you want to output only the missing rows?

[cyclogenisis]

Correct.

[ArndW]

My first thought is a to use stage variables for this. This example only caters for one missing element, to do more you would need to incorporate a transform stage loop

Code: Select all

svMissingRow     if (in.A=svLastColumnA) AND (in.B-svLastColumnB>1) THEN 1 ELSE 0
svLastColumnA   in.A
svLastColumnB   in.B

Put a constraint for "svMissingRow".

[asorrell]

I've also seen jobs where they generated a "Perfect" set of rows (all required elements), then looped that back into the main data stream to a Merge stage that then output the unmatched "rejects".

[chulett]

Interesting... not sure I would have thought of that approach.