Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
if your main concern is using autonumber in Group by that should absolutely work!!!
Yes, should work (but why not validate the output?).
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;
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
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