comparing the 2 columns and output the greater

pavan_test · Post by **pavan_test** » Thu Mar 29, 2007 5:10 pm

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

us1aslam1us · Post by **us1aslam1us** » Thu Mar 29, 2007 5:37 pm

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?

pavan_test · Post by **pavan_test** » Thu Mar 29, 2007 5:52 pm

[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

Maveric · Post by **Maveric** » Thu Mar 29, 2007 11:10 pm

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.

pavan_test · Post by **pavan_test** » Fri Mar 30, 2007 9:06 am

[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

kwwilliams · Post by **kwwilliams** » Fri Mar 30, 2007 9:29 am

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

pavan_test · Post by **pavan_test** » Fri Mar 30, 2007 9:39 am

[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

pavan_test · Post by **pavan_test** » Fri Mar 30, 2007 9:49 am

[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

kwwilliams · Post by **kwwilliams** » Fri Mar 30, 2007 10:31 am

Thought I saw in your profile you were running an older version. Glad it worked for you.

DSXchange