Hi,
My requirement is like below.
Input:
EmpId Year sal
1 1980 1000
1 1981 2000
2 1980 3000
2 1981 4000
My Output is like below
Output:
EmpId 1980 1981
1 1000 2000
2 3000 4000
Please assist me on this.
Thanks in advance.
Convert Column values into column names
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 12
- Joined: Sun Sep 23, 2007 12:21 am
- Location: chennai
Its possible.. First use a filter stage using two where class namely year=1981 and year =1980. In the filter output1 change year as year1 and in filter output2 change year as year2, same for sal also change as sal1 & sal2. Now merge the two filtered output using a merge stage with empid as key. Then in the output column enter the colums as empid,1980,1981. Now in the output mapping map as below,
Empid------>Empid
sal1--------->1980
sal2---------->1981
Try this out.. thanks
Empid------>Empid
sal1--------->1980
sal2---------->1981
Try this out.. thanks
dineshm
-
- Participant
- Posts: 12
- Joined: Sun Sep 23, 2007 12:21 am
- Location: chennai
Its possible.. First use a filter stage using two where class namely year=1981 and year =1980. In the filter output1 change year as year1 and in filter output2 change year as year2, same for sal also change as sal1 & sal2. Now merge the two filtered output using a merge stage with empid as key. Then in the output column enter the colums as empid,1980,1981. Now in the output mapping map as below,
Empid------>Empid
sal1--------->1980
sal2---------->1981
Try this out.. thanks
Empid------>Empid
sal1--------->1980
sal2---------->1981
Try this out.. thanks
dineshm
Re: Convert Column values into column names
Input ---> Transformer ---> Two links from Transformer i.e. Link1 and Link2
Link1 ( only the year column ) -----> Remdup (on the years ) ----- Transformer
Transformer writes a single record which contains all the distinct years concatenated with any delimter ( assume it to be pipe) and Text 'EmpId' also added at the start and separated by the same delimiter. This link will have the following output:-
Column1
EmpID|1980|1981|.....any number of years
Link1 ----> Remdup ----> Transformer ----- Outlink1
I am just assuming the name of this output link as Outlink1 which is going to have only one row and one column.
Link2 from the transformer should go to a sort stage which just sort the date on the basis of EmpID, year and The output from sort would go to the transformer which then writes the record to the output only when the key is changing, transformer is going to concatenate the EmpId with salary for the same EmpId. This link should have the following output:-
Column1
1|1000|2000|.....
2|3000|4000|.....
Link2 -----> Sort Stage ----- Transformer ---- Outlink2
Just assume the name of this output link as Outlink2. This link is going to have one column only but the number of rows would be equal to the number of Distinct Emp Ids.
Funnel the output of both the links with first read from Outlink1.
Assumptions in this approach:-- Every emp-id is having an entry for each year, if you dont have that scenario then will try another approach to solve this problem.
Link1 ( only the year column ) -----> Remdup (on the years ) ----- Transformer
Transformer writes a single record which contains all the distinct years concatenated with any delimter ( assume it to be pipe) and Text 'EmpId' also added at the start and separated by the same delimiter. This link will have the following output:-
Column1
EmpID|1980|1981|.....any number of years
Link1 ----> Remdup ----> Transformer ----- Outlink1
I am just assuming the name of this output link as Outlink1 which is going to have only one row and one column.
Link2 from the transformer should go to a sort stage which just sort the date on the basis of EmpID, year and The output from sort would go to the transformer which then writes the record to the output only when the key is changing, transformer is going to concatenate the EmpId with salary for the same EmpId. This link should have the following output:-
Column1
1|1000|2000|.....
2|3000|4000|.....
Link2 -----> Sort Stage ----- Transformer ---- Outlink2
Just assume the name of this output link as Outlink2. This link is going to have one column only but the number of rows would be equal to the number of Distinct Emp Ids.
Funnel the output of both the links with first read from Outlink1.
Assumptions in this approach:-- Every emp-id is having an entry for each year, if you dont have that scenario then will try another approach to solve this problem.