Max of the Data
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 273
- Joined: Wed Oct 18, 2006 12:20 pm
- Location: Porto
Max of the Data
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.
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.
-
- Premium Member
- Posts: 273
- Joined: Wed Oct 18, 2006 12:20 pm
- Location: Porto
-
- Premium Member
- Posts: 273
- Joined: Wed Oct 18, 2006 12:20 pm
- Location: Porto
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 .
Last edited by asitagrawal on Fri Nov 03, 2006 11:30 am, edited 1 time in total.
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
-
- Premium Member
- Posts: 273
- Joined: Wed Oct 18, 2006 12:20 pm
- Location: Porto
No No,DeepakCorning wrote: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.
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.
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
-
- Premium Member
- Posts: 273
- Joined: Wed Oct 18, 2006 12:20 pm
- Location: Porto
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]DeepakCorning wrote: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.
-
- Premium Member
- Posts: 273
- Joined: Wed Oct 18, 2006 12:20 pm
- Location: Porto
Hi,gateleys wrote: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
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
asitagrawal wrote:Also explain me where to find abt the 'Last' in Aggregator Actions. What does this action do?
![Confused :?](./images/smilies/icon_confused.gif)
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers