How to use Pivot stage

[svhari76]

Hi

I have a text file with approximately 20 variable length columns max. First 10 columns are always present and the next 10 columns are variable i.e i can get 14 columns in a row and 18 columns in next row etc.. when i am defining the sequencial file read i defined 20 columns by default and want to use pivot to convert each column after 10 th column would be a row.

Eg: A,B, C, 1,2,3 to be
A,B,C, 1
A,B,C, 2
A,B,C, 3 etc...

But my first seq file read stage fails because of not having sufficient data in the fole for each row(expecting 20 fields and have only 14 columns for the first row etc.Even i tried to use Default null string as "" and Map empty string option to make missing columns as empty, but it did not work.

Can some one please let meknow how i can over come this issue.

[ray.wurlod]

Scroll to the right in the Columns grid until you find the "Incomplete Column" rule. Working up from the final column, change the possibly missing columns' rule to "Replace", which will replace each with NULL. Once you've done the pivot you can filter out the rows that have null, if that fits with the business requirement.