DSXchange

Posted: **Wed Sep 20, 2006 11:33 pm**

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

Posted: **Thu Sep 21, 2006 3:37 am**

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

Posted: **Thu Sep 21, 2006 4:05 am**

vjeran wrote: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

Posted: **Thu Sep 21, 2006 4:57 am**

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

Posted: **Thu Sep 21, 2006 5:00 am**

Hi,

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

BR Vjeran

Posted: **Thu Sep 21, 2006 5:22 am**

vjeran wrote: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

Posted: **Thu Sep 21, 2006 5:34 am**

Hi,
so it should be:

Code: Select all

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

Posted: **Thu Sep 21, 2006 5:38 am**

vjeran wrote: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

Posted: **Thu Sep 21, 2006 6:07 am**

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

Posted: **Thu Sep 21, 2006 6:34 am**

vjeran wrote: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

Posted: **Thu Sep 21, 2006 7:02 am**

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

Posted: **Thu Sep 21, 2006 8:34 am**

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

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

Posted: **Thu Sep 21, 2006 9:12 am**

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

Posted: **Thu Sep 21, 2006 9:48 am**

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

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

Posted: **Thu Sep 21, 2006 3:09 pm**

Yes. Same rules as SELECT in SQL.

DSXchange

Aggregator transformation

Aggregator transformation