Page 1 of 1
Transpose/pivot logic needed
Posted: Thu May 03, 2012 10:30 am
by pdntsap
Hello,
We have a requirement to transpose data as follows:
Code: Select all
Column1 Column2 Column3 Column4 Column5
Row1 a b c 1 1000
Row2 a b c 2 2000
Row3 a b c 3 3000
We need to transpose the input and produce:
Code: Select all
Column1 Column2 Column3 1 2 3
Row1 a b c 1000 2000 3000
The values present in Column4('1','2','3') become column names of the transposed data. I believe I might need to use the pivot stage but having difficulty in converting column values to column names. Any help is highly appreciated.
Thanks.
Posted: Thu May 03, 2012 4:11 pm
by ray.wurlod
Simply hard code the column names "c1", "c2", "c3". DataStage will not permit numeric column names. You can change them downstream if necessary (and in row #1 only).
Posted: Thu May 03, 2012 9:14 pm
by pdntsap
Thanks Ray.
I had one more questions. According to the requirements, the number of rows can be between 1 and 4. For example:
Code: Select all
Column1 Column2 Column3 Column4 Column5
Row1 a b c c1 1000
Row2 a b c c2 2000
Row3 a b c c3 3000
Row4 a b c c4 4000
Row5 d e f c1 5000
Row6 d e f c3 6000
Row7 d e f c4 7000
The above data should be transposed as:
Code: Select all
Column1 Column2 Column3 c1 c2 c3 c4
Row1 a b c 1000 2000 3000 4000
Row2 d e f 5000 NULL 6000 7000
Any suggestions on implementing the above is also greatly appreciated.
Thanks.
Posted: Thu May 03, 2012 9:24 pm
by qt_ky
It looks similar to the parallel developer product manual examples of Transformer loop variables in 8.5 and higher.
Posted: Thu May 03, 2012 9:25 pm
by kandyshandy
Which version of DataStage you are using? If it is >= 8.5, then you can use vertical pivot method in pivot stage. In this case, you need to assume that you will always have 4 rows coverted to 4 columns. Any concern on null fields for groups having less than 4 rows?
Or
Use stage variables in transfomer stage (you can search the forum for "vertical pivot"). Sort & select the required record.
Or
Do it is BASIC language or C++.
Posted: Fri May 04, 2012 4:21 am
by pandeesh
The logic to apply nulls for those having less than 4 records is not tacit.
If the maximum number of records per group is fixed, then we can use that and decide the maximum no of columns to be created .
Posted: Fri May 04, 2012 7:14 am
by pdntsap
Which version of DataStage you are using?
My version is 8.7.
Any concern on null fields for groups having less than 4 rows?
The transposed data must have four columns for each group with the value being NULL for any missing rows.
Thanks for the suggestions and I will try them out.
Posted: Fri May 04, 2012 2:47 pm
by pdntsap
I looked at the solution provided in the following thread:
viewtopic.php?t=145258&highlight=vertical+pivot
I was under the assumption that the LastRowInGroup() funtion in a Transformer stage will work on more than one input column concatenated together. But, looks like my assumption is wrong and the LastRowInGroup() funtion takes only one input column as a parameter. Is that so?
Thanks.
Posted: Fri May 04, 2012 6:00 pm
by qt_ky
It takes one column and the column must first be sorted. You could concat many columns into one column, sort on it, then use the function on it.