topic Re: Data aggregation / Group By question in QlikView

Data aggregation / Group By question

tduarte — Thu, 02 Apr 2015 16:05:26 GMT

Hi,

I was trying to do a Group By similar to SQL when the load contains an if condition.

It was giving me an "Invalid Expression" error so I added all fields not aggregated in the Group By.

DataTemp:

LOAD * INLINE [

Vendor, fail, Units

A, 1, 1

B, 1, 2

C, 0, 3

D, 0, 4

D, 0, 10

E, 0, 5

];

Data:

LOAD

if(fail=1,'Other',Vendor) as Vendor,

Sum(Units) as Units

Resident DataTemp

Group by Vendor, fail;

Drop Table DataTemp;

=========================================

Result:

Vendor	Units
Other	1
Other	2
E	5
D	14
C	3

The code works but it's not aggregating all rows as I intended.

What I want is this:

Vendor	Units
Other	3
E	5
D	14
C	3

Is there a way of achieving this without doing another Resident Load with another Group By?

Re: Data aggregation / Group By question

Anonymous — Thu, 02 Apr 2015 16:25:02 GMT

How using a preceding load, maybe something like this ?

DataTemp:

LOAD * INLINE [

Vendor, fail, Units

A, 1, 1

B, 1, 2

C, 0, 3

D, 0, 4

D, 0, 10

E, 0, 5

];

Data:

load

Vendor,

Sum(Units) as Units

Group by Vendor

;

LOAD

if(fail=1,'Other',Vendor) as Vendor,

Units

Resident DataTemp

;

Drop Table DataTemp;

Re: Data aggregation / Group By question

MK_QSL — Thu, 02 Apr 2015 16:41:15 GMT

DataTemp:

Load

if(fail = 1, 'Other',Vendor) as Vendor,

fail,

Units

Inline

[

Vendor, fail, Units

A, 1, 1

B, 1, 2

C, 0, 3

D, 0, 4

D, 0, 10

E, 0, 5

];

Data:

Load

Vendor,

SUM(Units) as TotalUnit Resident DataTemp

Group By Vendor,fail;

Drop Table DataTemp;

Re: Data aggregation / Group By question

buzzy996 — Thu, 02 Apr 2015 16:47:53 GMT

u can also try this,

DataTemp:

LOAD * INLINE [

Vendor, fail, Units

A, 1, 1

B, 1, 2

C, 0, 3

D, 0, 4

D, 0, 10

E, 0, 5

];

DataTemp2:

Noconcatenate LOAD

if(fail=1,'Other',Vendor) as Vendor,

Units,

fail

Resident DataTemp;

Drop table DataTemp;

Data:

Noconcatenate Load Vendor,

Sum(Units) as Units,

fail

Resident DataTemp2

Group by Vendor,fail;

Drop table DataTemp2;

Re: Data aggregation / Group By question

anbu1984 — Fri, 03 Apr 2015 03:57:04 GMT

LOAD
  Vendor,
  Sum(Units) as Units
Group by Vendor, fail;
LOAD if(fail=1,'Other',Vendor) as Vendor,Units,fail INLINE [
    Vendor, fail, Units
    A, 1, 1
    B, 1, 2
    C, 0, 3
    D, 0, 4
    D, 0, 10
    E, 0, 5
];

Re: Data aggregation / Group By question

Fri, 03 Apr 2015 04:19:26 GMT

You can try like this:

DataTemp:
Load if(fail=1,'Other',Vendor) as Vendor, Units;
LOAD * INLINE [

Vendor, fail, Units

A, 1, 1

B, 1, 2

C, 0, 3

D, 0, 4

D, 0, 10

E, 0, 5

];

Data:
Load Vendor,Sum(Units) as Sum_Units
Resident DataTemp
Group by Vendor;

output:

Vendor	Sum_Units
C	3
D	14
E	5
Other	3

Re: Data aggregation / Group By question

tduarte — Tue, 07 Apr 2015 09:17:42 GMT

It now looks really obvious and I should know better but I completely forgot one could do a Group By on a preceding load. Makes sense!

Opted for anbu1984 response as it is the cleanest and the one that is closest to my real case scenario.

Thank you all for your suggestions!