I have 6 columns in my file.
ID,CODE1,CODE2,CODE3,CODE4,CODE5
123,300,200,500,600,20
123,400,40,30,300,20
I need the output like below
ID,CODE1,CODE2,CODE3,CODE4,CODE5
123,300,200,500,600,20,400,40,30,300,20
Can any one have any idead about how to do this in datatsage
Thanks
need logic
Moderators: chulett, rschirm, roy
Re: need logic
dstest wrote:I need the output like below
ID,CODE1,CODE2,CODE3,CODE4,CODE5
123,300,200,500,600,20,400,40,30,300,20
How many output columns you have ???
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Charter Member
- Posts: 36
- Joined: Thu Jun 10, 2004 11:22 pm
if I can infer by your example, you want to concatenate lines that have same ID.
Here's how i'd do it:
1. read all fields into 1 long varchar --> file1
2. using Field() read out ID, & write to hashedfile HF_id (ID, str1)
3. in another job, src=file1 ; lookup: HF_id ; out=HF_id (key=ID)
in Tfm, if ID match found --> concatenate existing value of HF_id.str1
try out & let us know..
Here's how i'd do it:
1. read all fields into 1 long varchar --> file1
2. using Field() read out ID, & write to hashedfile HF_id (ID, str1)
3. in another job, src=file1 ; lookup: HF_id ; out=HF_id (key=ID)
in Tfm, if ID match found --> concatenate existing value of HF_id.str1
try out & let us know..
-
- Charter Member
- Posts: 36
- Joined: Thu Jun 10, 2004 11:22 pm
sorry i meant to say that use Server job to reconstruct the file..htrisakti3 wrote:if I can infer by your example, you want to concatenate lines that have same ID.
Here's how i'd do it:
1. read all fields into 1 long varchar --> file1
2. using Field() read out ID, & write to hashedfile HF_id (ID, str1)
3. in another job, src=file1 ; lookup: HF_id ; out=HF_id (key=ID)
in Tfm, if ID match found --> concatenate existing value of HF_id.str1
try out & let us know..
-
- Participant
- Posts: 25
- Joined: Fri Jan 11, 2008 12:49 am
- Location: Pune, India
You can also try this with a PX job:
1. Sort the input based on the ID column and generate Rank_Num starting from 1 wherever the ID changes.
2. Use a filter stage and have 2 outputs from it. First one filters outs all rows with Rank_Num=1 and second one filters out all rows with Rank_Num<>1.
3. Use a Join stage with a Left outer condition. Left link would be the row with Rank_Num=1. Take the required columns from the other rows on the right link.
Let us know if this works.
Regards,
Dhanashree
1. Sort the input based on the ID column and generate Rank_Num starting from 1 wherever the ID changes.
2. Use a filter stage and have 2 outputs from it. First one filters outs all rows with Rank_Num=1 and second one filters out all rows with Rank_Num<>1.
3. Use a Join stage with a Left outer condition. Left link would be the row with Rank_Num=1. Take the required columns from the other rows on the right link.
Let us know if this works.
Regards,
Dhanashree
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: