Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
tduarte
Contributor II

Data aggregation / Group By question

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:

VendorUnits
Other1
Other2
E5
D14
C3

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

What I want is this:

VendorUnits
Other3
E5
D14
C3

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

Tags (1)
1 Solution

Accepted Solutions
anbu1984
Honored Contributor III

Re: Data aggregation / Group By question

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

];

6 Replies

Re: Data aggregation / Group By question

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;

MVP
MVP

Re: Data aggregation / Group By question

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;

buzzy996
Honored Contributor II

Re: Data aggregation / Group By question

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;

anbu1984
Honored Contributor III

Re: Data aggregation / Group By question

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

];

Not applicable

Re: Data aggregation / Group By question

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:

   

VendorSum_Units
C3
D14
E5
Other3

tduarte
Contributor II

Re: Data aggregation / Group By question

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!

Community Browser