topic Re: Group By in QlikView

Group By

Wed, 27 Feb 2013 11:34:10 GMT

Hola a todos,

Estoy intentando hacer una inner Join entre dos tablas y agrupar después el resultado:

Usuario:

LOAD Id as IDUsuario__c,

Username;

SQL SELECT Id,

Username,

FROM User;

Inner Join (Usuario)

Comentario:

LOAD CreatedById as IDUsuario__c,

Count(Id) as Num;

SQL SELECT CreatedById,

FROM UserFeed

Group By CreatedById;

Me dice que agrupe también por el Id de la tabla Comentario, pero si pongo Group By CreatedById,Id tampoco funciona. No sé porqué no me salen datos.

Podéis ayudarme?

Gracias

César

Re: Group By

Gysbert_Wassenaar — Wed, 27 Feb 2013 11:43:08 GMT

Comentario:

LOAD CreatedById as IDUsuario__c,

Num;

SQL SELECT

Id,

Count(Id) as Num

FROM UserFeed

Group By Id;

Re: Group By

Wed, 27 Feb 2013 11:49:36 GMT

Hi Gysbert,

Your answer don't work. The error says:

ERROR at Row:3:Column:20

unexpected token: as

Thanks,

César

Re: Group By

Gysbert_Wassenaar — Wed, 27 Feb 2013 11:55:53 GMT

Maybe like this:

Comentario:

LOAD CreatedById as IDUsuario__c,

Num;

SQL SELECT

CreatedById,

Count(Id) as Num

FROM UserFeed

Group By CreatedById;

It's not entirely clear to me if you have a CreatedById and an Id field or both in your UserFeed table.

Re: Group By

Wed, 27 Feb 2013 12:01:54 GMT

Hi,

I have both in the table. The CreatedById field is the one that show who has created a comment. And I want to use the Id field to count how many comments has been posted by each person.

The same error still appears.

Thanks

Re: Group By

Gysbert_Wassenaar — Wed, 27 Feb 2013 12:20:00 GMT

Hmm, your database doesn't understand the as keyword? That's a bit weird.

Comentario:

LOAD CreatedById as IDUsuario__c,

Num;

SQL SELECT

CreatedById,

Count(Id) Num

FROM UserFeed

Group By CreatedById;

Re: Group By

Wed, 27 Feb 2013 12:47:39 GMT

But in the rest of the script understands

Re: Group By

Wed, 27 Feb 2013 13:51:26 GMT

It could be because I'm connected to Salesforce?

Re: Group By

smqlikview — Sat, 02 Mar 2013 00:30:41 GMT

Remove SQL from "SQL SELECT" and try.

Re: Group By

smqlikview — Sat, 02 Mar 2013 00:30:54 GMT

Remove SQL from "SQL SELECT" and try.

Re: Group By

Mon, 04 Mar 2013 11:19:48 GMT

Hi,

First, thanks smqlikview.

I think this error is because i'm consulting on a Force.com database. Well, now i'm here:

Comentario:

LOAD CreatedById;

SQL SELECT CreatedById , count (Id) NumeroTotal

FROM UserFeed Group By CreatedById;

It does the filter but don't write anithing in the table.

Thanks

Cesar

Re: Group By

smqlikview — Mon, 04 Mar 2013 17:07:51 GMT

Hi Cesar:

If you have loaded another table with the same data structure (that is, a table with CreatedByID as its sole column), then Qlikview might be appending the rows returned by the SQL to that table. You can try one of these two options to check if that is happening:

1. Create a new qvw and in its script just have this statement. See if that is creating the table. If it does not show the rows you are looking for, then probably the error is in the select statement. I do not know much about force.com to guide you on it. But your sql statement looks right. You can try removing the "SQL" before "select" and execute the script in the new qvw, and see if that works.

2. Second option is, add "noconcatenate" (in your current qvw script; no need to create a new one), as shown below:

Comentario:

NOCONCATENATE

LOAD CreatedById;

SQL SELECT CreatedById , count (Id) NumeroTotal

FROM UserFeed Group By CreatedById;

Goodluck.

Re: Group By

Tue, 05 Mar 2013 10:56:44 GMT

Hi smqlikview,

I did both and failed both too. I think that Group By do not run with qlikview (salesforce) connector.

Thanks,

Cesar