topic Re: Autonumber function in group by clause in QlikView

Autonumber function in group by clause

jyothish8807 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

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

Load

autonumber(A & B) as Key,

sum(D) as D

from table1

group by autonumber(A & B),C;

Br,

Re: Autonumber function in group by clause

aarkay29 — Mon, 18 Sep 2017 17:21:36 GMT

Table:

Load

autonumber(A & B) as Key,

from table1;

Left Join (Table)

Load

Key,

sum(D) as D

from table1

group by Key,C

Resident Table;

Re: Autonumber function in group by clause

jyothish8807 — Mon, 18 Sep 2017 17:26:03 GMT

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,

Re: Autonumber function in group by clause

aarkay29 — Mon, 18 Sep 2017 17:36:00 GMT

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

Re: Autonumber function in group by clause

swuehl — Mon, 18 Sep 2017 17:36:25 GMT

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

Re: Autonumber function in group by clause

trdandamudi — Mon, 18 Sep 2017 17:36:57 GMT

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;

Re: Autonumber function in group by clause

jyothish8807 — Mon, 18 Sep 2017 17:38:49 GMT

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

Re: Autonumber function in group by clause

MarcoWedel — Mon, 18 Sep 2017 22:53:54 GMT

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