Max of the Data

[asitagrawal]

Hi,

In my Job,
corresponding to values from a Seq File, my Database will return say 10 rows.
Now i want to do gp by on these 10 rows.
Similarly for the next row of data coming from the File, another set of some rows will be returned and a Gp by will be done on them too.
I dont have to combine the Gp By results of both the sets of data.


How do I do it?

Thx.

[chulett]

Your issue is a little... hard to follow. How about some examples? Post some samples of input data and what you want to come out the other end.

[asitagrawal]

Your issue is a little... hard to follow. How about some examples? Post some samples of input data and what you want to come out the other end.

Please chk my post, topic = 'Group By in DB2 Stage and Ref from hash File ', and see if that explains sth.

[chulett]

Not really... which is why you got the 'use the Aggregator' response. Most people aren't going to burn alot of brain cells trying to puzzle out what the heck you mean, so throw us a bone here. Post examples.

[asitagrawal]

This is an example, hope it expalins sth:

Code: Select all

A SQL Record (reference link)		
F1#	F2#	F3#	F4#
1	2	3	4
1	2	4	5
2	3	4	3
2	3	5	8
2	3	6	9
3	4	1	2
4	1	2	3

Hash File (Source or Stream Link)			
f1	f2		
1	2		
2	3		
3	4		
4	1		
			
for f1=1 and f2=3, from 'A' I get
F1#	F2#	F3#	F4#
1	2	3	4
1	2	4	5

So I want the below output for each row coming from the hash file.

Output			
F1#	F2#	Max(F3#)Max(F4#)
1	2	4	5
2	3	6	9
3	4	1	2
4	1	2	3

Max values are found from the set of values returned by matching F1# with f1 and F2# with f2	.	

[DeepakCorning]

Can not you do this before putting the data in the hashed file ==

Select F1#,F2#,MAX(F3#),MAX(F4#) from A group by F1#,F2#

That way your hashed file will have only one record corresponding to the F! and F2 fields and then you can do the lookup with out any comlex logic.

Hope this makes sense.

[asitagrawal]

Can not you do this before putting the data in the hashed file ==

Select F1#,F2#,MAX(F3#),MAX(F4#) from A group by F1#,F2#

That way your hashed file will have only one record corresponding to the F! and F2 fields and then you can do the lookup with out any comlex logic.

Hope this makes sense.

No No,

the 'A' SQL record is my lookup and the hash file is the source. Now for each row of Hash File, the 'A' SQL record will return a set of rows (F1#,F2#,F3#,F4#). Now I have to find the Max(F3#) and Max(F4#) from this set of data.

[DeepakCorning]

Can you explain in a small simple mapping view kind of thing the above requirement like --

Hashed File
|
|
A ---- Trans ------


Because I am now confused , and will like to know why we can not perform the MAX thing.

[asitagrawal]

Can you explain in a small simple mapping view kind of thing the above requirement like --

Hashed File
|
|
A ---- Trans ------


Because I am now confused , and will like to know why we can not perform the MAX thing.

We cannot do the MAX thing coz of 'Row By Row' processing becosue of which the DB Stage will not return the SET of data which shud be returned for the mathcing keys.[/img]

[gateleys]

Perform a join with the keys (F1,F2). Pass the output to an Aggregator. Group by (F1,F2). For the derivation of F3 and F4, use 'Last' (Assuming they are sorted...if not, use MAX().

gateleys

[asitagrawal]

Perform a join with the keys (F1,F2). Pass the output to an Aggregator. Group by (F1,F2). For the derivation of F3 and F4, use 'Last' (Assuming they are sorted...if not, use MAX().

gateleys

Hi,

Please chk the example in the the post above.

Also explain me, that since the result of the query from the DB2 Stage will be a SET of rows, will all the rows of that SET be processed, coz I feel that only one row can move on a link at one time and not a SET if data.

Also explain me where to find abt the 'Last' in Aggregator Actions. What does this action do?

Thx

[chulett]

Also explain me where to find abt the 'Last' in Aggregator Actions. What does this action do?

It does exactly what it sounds like it would do. It is part of a related series of actions that don't technically do any 'aggregation':

First
Last
Max
Min

Each one works within each 'group' you are aggregating on. For each aggregation group, it takes the corresponding field value from the rows that made up that group - the first value, the last value, etc - and passes that value out.