Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
jyothish8807
Master II
Master II

Autonumber function in group by clause

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

Best Regards,
KC
7 Replies
aarkay29
Specialist
Specialist

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
Master II
Master II
Author

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

Best Regards,
KC
aarkay29
Specialist
Specialist

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

swuehl
MVP
MVP

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

trdandamudi
Master II
Master II

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
Master II
Master II
Author

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

Best Regards,
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