duplicate rows input for unique value output

[Former Member]

Hi,

I have source table that contain 3 column and 2 records like the following example:

c1     c2     c3

1      100    10

1      200    5

i need that the output will take only one row for each c1 value

and to take the record  with the max value in c2 (c1 suppose to be key in output)

so in my example i want to take the second row

so in the query after the source i can do order by c1 , c2  and now i know that the last row for each c1 value is the row for the output.

is there any mechanisem in data service for this issue  that pass over the records and if the previous  row contain the same value key in output

then it's doing overwirte to the record ?

in my example i want that the second row will update the first row and the output will contain only one row

the target in me example is file and not table

regards,

Ilan Haik

[Former Member · Accepted solution]

Hi llan Haik,

     i have designed a job according to your requirement,as below

my source is

in grpby_max query i did like this

    in groupby clause i am grouping the eno  to find the maximum sal in that group

for sal column i used max function to find the maximum valu in the group

the query output will be like this

to bring the remaining columns from the source we have the join the grpby_max query with source

in join query as show in first pic

the output will be like this

[Former Member]

Hi,

Since you are passing only one record in the output, where's the question of updating the first record with second?

If you want to update the first record with the second, you can use a lookup to catch all the second records and update the first with the second where key matches before loading. Else, you can also try a post load sql command to update the records and load it in a table and in the second dataflow convert the table to a file.

Arun

Message was edited by: Arun Kumar