Aggregator transformation
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 145
- Joined: Fri Sep 15, 2006 12:23 pm
Aggregator transformation
Hi,
Is there a possibility to pass the columns through aggregator transformation without being aggregated.
the scenario:
there are 3 columns F1---->string, F2----->Date, F3----->String. I want to pick the last entry of F1 based on F2 column. so, i pass the data from aggregator by grouping F1 column and finding max(F2) column. but i dont want to perform any sort of aggregation on F3. If i dont perform the aggregation i wouldnt be able to pass F3 but in the output i need F3 how do i get that????
Is there a possibility to pass the columns through aggregator transformation without being aggregated.
the scenario:
there are 3 columns F1---->string, F2----->Date, F3----->String. I want to pick the last entry of F1 based on F2 column. so, i pass the data from aggregator by grouping F1 column and finding max(F2) column. but i dont want to perform any sort of aggregation on F3. If i dont perform the aggregation i wouldnt be able to pass F3 but in the output i need F3 how do i get that????
Hi,
if you don't put agregation or grouping on column F3 which value you want to transfer to next step ?
eg:
...or do you want to transfer something like this :
BR Vjeran
if you don't put agregation or grouping on column F3 which value you want to transfer to next step ?
eg:
Code: Select all
F1 F2 F3
---------------
1 2 3
1 4 5
6 7 8
6 9 10
select F1, max (F2) from Table group by F1
F1 max (F2) F3
---------------------------
1 4 3 or 5
6 9 8 or 10
Code: Select all
F1 max (F2) F3
---------------------------
1 4 3
1 4 5
6 9 8
6 9 10
-
- Premium Member
- Posts: 145
- Joined: Fri Sep 15, 2006 12:23 pm
I want it this way.vjeran wrote:Hi,
if you don't put agregation or grouping on column F3 which value you want to transfer to next step ?
eg:...or do you want to transfer something like this :Code: Select all
F1 F2 F3 --------------- 1 2 3 1 4 5 6 7 8 6 9 10 select F1, max (F2) from Table group by F1 F1 max (F2) F3 --------------------------- 1 4 3 or 5 6 9 8 or 10
BR VjeranCode: Select all
F1 max (F2) F3 --------------------------- 1 4 3 1 4 5 6 9 8 6 9 10
Code: Select all
F1 max (F2) F3
---------------------------
1 4 5
6 9 10
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 145
- Joined: Fri Sep 15, 2006 12:23 pm
Hi,
so it should be:
so it should be:
Code: Select all
select F1, max (F2), last (F3) fom Table group by F1
-
- Premium Member
- Posts: 145
- Joined: Fri Sep 15, 2006 12:23 pm
i needvjeran wrote:Hi,
so it should be:Code: Select all
select F1, max (F2), last (F3) fom Table group by F1
Code: Select all
select F1, max (F2), F3 from Table group by F1
-
- Premium Member
- Posts: 145
- Joined: Fri Sep 15, 2006 12:23 pm
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Premium Member
- Posts: 145
- Joined: Fri Sep 15, 2006 12:23 pm
Apologies!!! I am not sure what you are trying to achieve here, could you please help me understand the logic???keshav0307 wrote:you can get the result by using combination of a SORT and transformation Stage.......
from file Sort on F1 Asc, F2 desc;
In Tnasform stage declare two stage variable VNext and VPriv
VNext=F1
Vpriv=Vnext
have two output from tansform
1st link constraint Vpriv<>Vnext
2nd link constraint Vpriv=Vnext
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
Its same funda as used to remove duplicate
your data is like
F1 F2 F3
---------------
1 2 3
1 4 2
6 7 8
6 9 4
after sorting you will get
F1 F2 F3
---------------
1 4 2
1 2 3
6 9 4
6 7 8
after the tansform one link will give you
F1 F2 F3
---------------
1 2 3
6 7 8
Other link will give you
F1 F2 F3
---------------
1 4 2
6 9 4
your data is like
F1 F2 F3
---------------
1 2 3
1 4 2
6 7 8
6 9 4
after sorting you will get
F1 F2 F3
---------------
1 4 2
1 2 3
6 9 4
6 7 8
after the tansform one link will give you
F1 F2 F3
---------------
1 2 3
6 7 8
Other link will give you
F1 F2 F3
---------------
1 4 2
6 9 4
-
- Premium Member
- Posts: 145
- Joined: Fri Sep 15, 2006 12:23 pm
Thanks keshav, i will try that tomorrow and let you know how it goes. And Finally can i conclude that each column that is passed through aggregator has to be either grouped or aggregated????keshav0307 wrote:Its same funda as used to remove duplicate
your data is like
F1 F2 F3
---------------
1 2 3
1 4 2
6 7 8
6 9 4
after sorting you will get
F1 F2 F3
---------------
1 4 2
1 2 3
6 9 4
6 7 8
after the tansform one link will give you
F1 F2 F3
---------------
1 2 3
6 7 8
Other link will give you
F1 F2 F3
---------------
1 4 2
6 9 4
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: