Aggregator transformation

[ahmedwaseem2000]

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????

[vjeran]

Hi,

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

...or do you want to transfer something like this :

Code: Select all

F1     max (F2)     F3 
---------------------------
1          4         3 
1          4         5
6          9         8 
6          9        10

BR Vjeran

[ahmedwaseem2000]

Hi,

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

...or do you want to transfer something like this :

Code: Select all

F1     max (F2)     F3 
---------------------------
1          4         3 
1          4         5
6          9         8 
6          9        10

BR Vjeran

I want it this way.

Code: Select all

F1     max (F2)     F3 
---------------------------
1          4         5
6          9        10

[ray.wurlod]

Well, that certainly looks like Max(F3) from where I'm sitting!

[vjeran]

Hi,

it looks like you need max agregation on F3 column, or I am missing something?

BR Vjeran

[ahmedwaseem2000]

Hi,

it looks like you need max agregation on F3 column, or I am missing something?

BR Vjeran

yes you guys are missing something.

look at this scenario:

F1 F2 F3
---------------
1 2 3
1 4 2
6 7 8
6 9 4

and i need

1 4 2
6 9 4

I hope i am clear enough now

[vjeran]

Hi,
so it should be:

Code: Select all

select F1, max (F2), last (F3) fom Table group by F1

[ahmedwaseem2000]

Hi,
so it should be:

Code: Select all

select F1, max (F2), last (F3) fom Table group by F1

i need

Code: Select all

select F1, max (F2), F3 from Table group by F1

[vjeran]

OK. What you need is to make sort on F1 and F2 before aggregation, which will give you correct value if function on F3 is LAST.
Depending on input, you can sort with SQL in input stage or make sort as a separate stage before aggregation.

BR Vjeran

[ahmedwaseem2000]

OK. What you need is to make sort on F1 and F2 before aggregation, which will give you correct value if function on F3 is LAST.
Depending on input, you can sort with SQL in input stage or make sort as a separate stage before aggregation.

BR Vjeran

but i am reading from sequential file

[keshav0307]

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

[ahmedwaseem2000]

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

Apologies!!! I am not sure what you are trying to achieve here, could you please help me understand the logic???

[keshav0307]

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

[ahmedwaseem2000]

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

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????

[ray.wurlod]

Yes. Same rules as SELECT in SQL.