Autonumber function in group by clause

[jyothish8807]

Hi All,

Can i use auto number in my group by clause statement ? Is the below statement correct ?

Load

autonumber(A & B) as Key,

C,

sum(D) as D

from table1

group by autonumber(A & B),C;

Br,

KC

[aarkay29]

Table:

Load

autonumber(A & B) as Key,

C,

D

from table1;

Left Join (Table)

Load

Key,

C,

sum(D) as D

from table1

group by Key,C

Resident Table;

[jyothish8807]

Hi Aar,

Thanks for your input.

This process will actually increase the load time.

The script i mentioned is not giving me error so was confused if it does work ?

Br,

KC

[aarkay29]

if your main concern is using autonumber in Group by  that should absolutely work!!!

[swuehl]

Yes, should work (but why not validate the output?).

[trdandamudi]

You can use it and I don't see any issue.  Here is a sample...

SourceData:
Load * Inline [
A,B,C,D
A,100,Type-A,100
B,200,Type-B,200
A,100,Type-A,300
C,400,Type-C,400
B,200,Type-B,500
];

NoConcatenate
Final:
Load
autonumber(A & B) as Key,
C,
sum(D) as D
Resident SourceData
group by autonumber(A & B),C;

Drop Table SourceData;

[jyothish8807]

Hi Stefan,

Actually i am dealing with huge amount of data and currently the load is taking a lot of time.

I am trying to understand if this is an optimized way of doing it or should i do something else.

Br,

Jyothish KC

[MarcoWedel]

Hi,

as "Autonumber(A&B)" delivers one value for each distinct combination of A and B, you might as well group by A,B instead.

Some testing suggests that

LOAD Autonumber(A&B) as Key,
    C,
    Sum(D) as D
Resident table1
Group By A,B,C;

delivers the same result in less time.

Besides that you could add a delimiter to avoid possible collisions (depending on your data😞

Autonumber(A&'/'&B) as Key

hope this helps

regards

Marco