Vertical Pivot Dynamic Columns

dslisa · Post by **dslisa** » Mon Oct 27, 2008 7:15 pm

Hi everyone,
I need some help to apply vertical pivot logic for dynamic data. I have achieved the vertical pivot and the data output is correct but now the business rule has changed and I will not have constant number of columns. Below is my job design.

Input Data

Key_Col Data_Col
1 name1
1 name2
1 name3
2 name1
2 name2

Seq. File-------->sort stage--------------->transformer------------->column import stage------------>Remove Duplicate------------>Output seq. file.

Output Data
1 name1 name2 name3
2 name1 name2

Transformer stage is using stage variable to concatenate the data and I am using Column Import Stage to split the data coming in a single row to multiple columns based on ",".
I have searched the forum but could not find a concrete resolution for changing number of columns.
Any idea regarding this is appreciated.

mail2hfz · Post by **mail2hfz** » Tue Oct 28, 2008 7:40 am

This has been discussed many times before. Do a search on the forum for "Vertical pivot".

dslisa · Post by **dslisa** » Tue Oct 28, 2008 7:48 am

True, the vertical pivot has been discussed many times before. But nothing concrete for vertical pivot for changing number of input. Has anybody implemented this?

ray.wurlod · Post by **ray.wurlod** » Tue Oct 28, 2008 7:58 am

In parallel jobs you need to configure for the maximum possible number of columns, assigning null to each unused column and subsequently filtering out the nulls. Or you might consider using a server job and the multi-value handling capabilities of a UniVerse stage to achieve a vertical pivot with an arbitrary number of columns.

dslisa · Post by **dslisa** » Tue Oct 28, 2008 8:04 am

Thanks Ray.
I am not supposed to use server jobs. So let us say the max number of columns I can have is 500. After the transformer stage in my job design my output would be like follows.

1 name1 name2...........name500.

But for this month let say I only have 200 columns. How to pad the rest 300 with null?

chulett · Post by **chulett** » Tue Oct 28, 2008 8:04 am

Or just a Server job with a hashed file being read from and written to by the same transformer. Easy Peasy.

ray.wurlod · Post by **ray.wurlod** » Tue Oct 28, 2008 9:34 am

Make the columns nullable and ensure that the default value is null.

Push back on the arbitrary restriction on your effectiveness. Server jobs are perfectly valid.

dslisa · Post by **dslisa** » Tue Oct 28, 2008 9:35 am

Hi Ray,
I assume from your previous post that I have to read the data as a long fixed length data. Each column is 100 characters long so for 500 columns it will be 500*100. Now the problem is lets say after 200 columns my data is padded with null. Even then after every 100 character column import stage needs to find a ",". How can I achieve this?

ray.wurlod · Post by **ray.wurlod** » Tue Oct 28, 2008 9:39 am

Nothing I posted implies fixed width.

dslisa · Post by **dslisa** » Tue Oct 28, 2008 10:57 am

Sorry about the confusion.
Let me explain my plan of action.
I thought as I already have the output as

1,name1,name2,name3
2,name1,name2
3,name1,name2,name3,name4

I could read each row as one long fixed width and pad the remaining character with null.
Say for example after name3 for 1st row, after name2 for 2nd row and so on.
Am I headed towards the right direction or towards the ditch?