comparing the 2 columns and output the greater
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
comparing the 2 columns and output the greater
Hi All,
I have 2 inputs say A and B. Both of them have the same metadata.i.e col1, col2, col3 and start_date.
start_date has a datatype:timestamp such as 3/01/2007 00:00:00
I have to read both the inputs and then compare start_date from A and B and whatever input has the greatest start_date I have to pass that value to the output along with the other columns col1,col2 and col3 from the same input.
A B
col1 col1
col2 col2
col3 col3
start_date start_date
if A.start_date is greater than B.start_date then output B.col1,B.col2,B.col3 and B.start_date.
can anyone please suggest me how do I do it in datatstage. I am using datastage parallel edition 7.0.1
Thanks
Mark
I have 2 inputs say A and B. Both of them have the same metadata.i.e col1, col2, col3 and start_date.
start_date has a datatype:timestamp such as 3/01/2007 00:00:00
I have to read both the inputs and then compare start_date from A and B and whatever input has the greatest start_date I have to pass that value to the output along with the other columns col1,col2 and col3 from the same input.
A B
col1 col1
col2 col2
col3 col3
start_date start_date
if A.start_date is greater than B.start_date then output B.col1,B.col2,B.col3 and B.start_date.
can anyone please suggest me how do I do it in datatstage. I am using datastage parallel edition 7.0.1
Thanks
Mark
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
Which records should be compared? For example the first record of file A should be compared to first of file B or second one or any other? You need to analyze that first. Do you have any of those three fields as Key Column?
I haven't failed, I've found 10,000 ways that don't work.
Thomas Alva Edison(1847-1931)
Thomas Alva Edison(1847-1931)
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
comparing the 2 columns and output the greater
[quote="us1aslam1us"]Which records should be compared? For example the first record of file A should be compared to first of file B or second one or any other? You need to analyze that first. Do you have any of those three fields as Key Column?[/quote]
There will be only 1 record in the input file A.
There will be only 1 record in the input file B.
The key column is: start_date
I have to compare A.start_date with B.stat_date. which ever is greater I have to map it to the output along with the other 3 columns from that input file.
Thanks
Mark
There will be only 1 record in the input file A.
There will be only 1 record in the input file B.
The key column is: start_date
I have to compare A.start_date with B.stat_date. which ever is greater I have to map it to the output along with the other 3 columns from that input file.
Thanks
Mark
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
comparing the 2 columns and output the greater
[quote="Maveric"]Create a dummy key colomn for both the input links and do a lookup on the dummy key column. the output is one rec with 6 fields. the comparision can be done in a transformer.[/quote]
after reading the input files A and B. I created a dummy column
in each of the inputs and did a look up based on dummy column. then
I output to the transformer. after lookup which input file do I have to output is it file A or file B.
can you please let me know in which transformer i have to do the
comparision..can you please elaborate for me..
thanks a bunch
mark
after reading the input files A and B. I created a dummy column
in each of the inputs and did a look up based on dummy column. then
I output to the transformer. after lookup which input file do I have to output is it file A or file B.
can you please let me know in which transformer i have to do the
comparision..can you please elaborate for me..
thanks a bunch
mark
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
Re: comparing the 2 columns and output the greater
If there is a funnel stage in 7.0.1 you could combine the two records together and then sort them by the date descending. Put them into a transformer with a contraint @OUTROWNUM = 1.
That should work, but I have 7.5.1 not 7.0.1
That should work, but I have 7.5.1 not 7.0.1
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
Re: comparing the 2 columns and output the greater
[quote="kwwilliams"]If there is a funnel stage in 7.0.1 you could combine the two records together and then sort them by the date descending. Put them into a transformer with a contraint @OUTROWNUM = 1.
That should work, but I have 7.5.1 not 7.0.1[/quote]
Thanks. actually i am using 7.5.2. I do have funnel stage. let me try what you have just suggested.
Thanks
Mark
That should work, but I have 7.5.1 not 7.0.1[/quote]
Thanks. actually i am using 7.5.2. I do have funnel stage. let me try what you have just suggested.
Thanks
Mark
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
Re: comparing the 2 columns and output the greater
[quote="kwwilliams"]If there is a funnel stage in 7.0.1 you could combine the two records together and then sort them by the date descending. Put them into a transformer with a contraint @OUTROWNUM = 1.
That should work, but I have 7.5.1 not 7.0.1[/quote]
Thanks a lot for all your suggestions. It is working now.
Right now both of my input files have only 1 record hence I will have no problems.
I will run the job again and verify with multiple records from the inputs files. i wil update you all with the results.
Thanks
Mark
That should work, but I have 7.5.1 not 7.0.1[/quote]
Thanks a lot for all your suggestions. It is working now.
Right now both of my input files have only 1 record hence I will have no problems.
I will run the job again and verify with multiple records from the inputs files. i wil update you all with the results.
Thanks
Mark
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
Re: comparing the 2 columns and output the greater
Thought I saw in your profile you were running an older version. Glad it worked for you.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com