Need to convert columns to rows
Moderators: chulett, rschirm, roy
Need to convert columns to rows
Hi,
I have the data under 2 columns
col1 col2
a 1
b xy
c 3
And the number of records under two columns differ from file to file.
I need to convert column data to records
i.e the output should be
a,b,c
1,xy,3 .
I tried this using vertical pivot but it is working only if there are fixed number if records.
I have the data under 2 columns
col1 col2
a 1
b xy
c 3
And the number of records under two columns differ from file to file.
I need to convert column data to records
i.e the output should be
a,b,c
1,xy,3 .
I tried this using vertical pivot but it is working only if there are fixed number if records.
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
Deepa, Your sample (input/output) data does not look like vertical pivot scenario. Do you have a group by column or not?
You can achieve vertical pivot in transformer (using stage variables to determine group change & concatenate columns).
Read IBM doc for more clarity.
You can achieve vertical pivot in transformer (using stage variables to determine group change & concatenate columns).
Read IBM doc for more clarity.
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
That makes sense. Now, try using stage variables to achieve the same. In transformer method, you don't need count of records. All you need is to find when a group changes. Your case is pretty simple as the group does not change at all. i.e. always "1"
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
Hi,
I implemented the following logic
I took a column pivot and assigned value 1.
Then in stage variables,i gave
current key=pivot
nextkey=currentkey
concat=if currentkey=nextkey then concat:lnk_input.col1 else ''
and it is giving me the following output.
,a
,a,b
,a,b,c--->need to read this row and remove comma infront.
in the constraint i cannot use pivot<>1 as pivot is always 1 in my case.
I implemented the following logic
I took a column pivot and assigned value 1.
Then in stage variables,i gave
current key=pivot
nextkey=currentkey
concat=if currentkey=nextkey then concat:lnk_input.col1 else ''
and it is giving me the following output.
,a
,a,b
,a,b,c--->need to read this row and remove comma infront.
in the constraint i cannot use pivot<>1 as pivot is always 1 in my case.
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
-
- Participant
- Posts: 91
- Joined: Wed Apr 20, 2005 7:59 pm
- Location: U.S.
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
Hi Kandy,kandyshandy wrote:Remove unwanted rows by adding another stage
Instead of dummy column pivot,i took a column called row_num
and assigned it to @inrownum system variable.
so that data from each file can have a unique key
i.e file1: ,a 1
,a,b 1
,a,b,c 1
file2: ,x 2
,x,y 2
,x,y,z 2
so that i can arrange them in descinding order and pick the first row for each key.
But when i read four files at a time,the @inrownum is not getting incremented.
the file1 is getting row_num 1
file2 2
file3 1
file4 2
that is row num is not incremented as 1,2,3,4 for my case to work.Please
suggest me on this.
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
Deepa, Your took a complicated route now.. @INROWNUM is the incoming ROW number from each parition. That's tricky.
Your earlier approach was simple for your requirements. Just assign 1 to pivot column and process in transformer stage. When done, sort by your new (concatenated) column in desc order and take the first record by using the appropriate stage
Your earlier approach was simple for your requirements. Just assign 1 to pivot column and process in transformer stage. When done, sort by your new (concatenated) column in desc order and take the first record by using the appropriate stage
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
Hi kandy,kandyshandy wrote:Just assign 1 to pivot column and process in transformer stage. When done, sort by your new (concatenated) column in desc order and take the first record by using the appropriate stage
But the earlier approach is giving me the problem when readng more than 2 files.
During concatination,it is concatenating the data coming in the same partition.
for example,
file1: col1 col2 file2:col1 col2 file3: col1 col2
a 1 x 1 p 1
b 2 y 2 q 2
c 3 z 3 r 3
result file:
a
a,b
a,b,c
a,b,c,p
a,b,c,p,q
a,b,c,p,q,r
x
x,y
x,y,z
so,if i sort in descinding order,i will get only a,b,c,p,q,r
but actually what i need is
a,b,c
p,q,r