topic Re: Group by with where clause in QlikView

Group by with where clause

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

Is there a way to use group by the same time as where clause?

I'd like to group by field1, resident Table1, but where field2 is 1, and field3 is 4.

Re: Group by with where clause

Gysbert_Wassenaar — Wed, 29 Jun 2016 14:15:56 GMT

Yes, you can use both a WHERE and an GROUP BY clause in the same load statement.

LOAD A, B, sum(C) as C

FROM ABC

WHERE C > 0

GROUP BY A, B

;

Re: Group by with where clause

Wed, 29 Jun 2016 14:21:44 GMT

I am doing this using a load resident, where can I put the where clause?

NewTable:

Load

field1,

Sum() as [SumC]

Resident [Table1]

Group by

field1;

Now where to add below?

where field2=1 and field3=4

Re: Group by with where clause

Gysbert_Wassenaar — Wed, 29 Jun 2016 14:26:21 GMT

NewTable:

Load

field1,

Sum() as [SumC]

Resident

[Table1]

Where

field2=1 and field3=4

Group by

field1

;

Re: Group by with where clause

sunny_talwar — Wed, 29 Jun 2016 14:26:32 GMT

May be this:

NewTable:

Load

field1,

Sum() as [SumC]

Resident [Table1]

Where field2 = 1 and field3 = 4

Group by field1;

Re: Group by with where clause

marcus_sommer — Wed, 29 Jun 2016 14:32:40 GMT

If you are dealing with large datasets you should better separate where and group by in two loadings - it will be much faster to filter at first the data and then to aggregate them.

- Marcus