Transposing input row into multiple rows

[mouthou]

Hi All,

I have a requirement to split each incoming row into 25 output rows. But those 25 output rows need to be created based on the 25 columns in each input row. This may look like pivoting but the difficulty is each such output row is to be created only if there is a valid value in the source columns.

Obvious solution could be to use a Transformer to find the length of the trimmed input char columns and if length > 0, then create a row. But I am concerned about creating 25 output links out of a Transformer. Please let me know if there is any other alternative or any length comparison facility in pivot stage itself. Appreciate your thoughts.


source file
----------------
cust id | src_col1| src_col2 |src_col3|.....|src_col18|...|src_col25
123 |1111 |<here blank spaces>|3333 |.....|1818 |...|2525


target DB
-----------
cust id | src_col
123 | 1111
123 | 3333
123 | 1818
123 | 2525


Thanks

[Thomas.B]

Did you try to use a transformer (or a filter) to validate the rows on the output link of the pivot stage?
Otherwise you can pivot the data using a looping transformer with only 2 input fields (concatenate all the "src_col" fields) and use the field() function to process the columns one by one.

[qt_ky]

Looping transformer as mentioned, plus a constraint to filter out the records with invalid values.

[ray.wurlod]

I'd be quite happy to specify 25 output links.

[mouthou]

Thanks everyone for the responses. In a way, the approaches using a Transformer only inevitably (be it 25 links or pivot or looping).

I went ahead with Pivot & Transformer combination though.

As Ray said, Transformer with 25 links could have worked well but it seemed to make the design bulky in addition to the internal code generation for that Transformer. This design will be really good as it creates a row in the output link only there is value. A similar option I was looking for without a Transformer!

[ray.wurlod]

A similar option I was looking for without a Transformer!

Why?

[qt_ky]

Maybe due to the urban legend that Transformer stage is slower or used to be slower than other stages...

[ray.wurlod]

The "used to be" part is not an urban legend. It was true. It was fixed.