How to use group by in Qlikview?

[#1]

Hi,

I would use group by like in sql but my code doesn't run.

I understand I must use group by with a resident table but impossible to run my app!!

load ID_DI,  count ([ID_Intervenant]) as [nb techniciens] resident tab  group by ID_OT;

Could help me please?

[Andrea_Ghirardello]

I think you should group by ID_DI instead of ID_OT

[#3]

Now, I use this code but it doesn't run!!

table4:

Load ID_OT,

     ID_DI,

     [Descriptif de l'intervention],

     [Code Type Intervention],

     [Code état OT],

     ID_Intervenant,

     [Date création OT],

     [Date début intervention],

     [Date fin intervention],

     [Date Départ Travail],

     [Date retour travail],

Count( ID_Intervenant ) as nb_technicien

resident OT

group by ID_OT ;

[#4]

Hi Xavier,

You don't need to use 'group by' in a resident load. You can use 'group by' in any load. However, 'Order By' must be used in a resident load.

Anyway, your problem is that you haven't loaded the field ID_OT. If that's what you want to group by then your load statement needs to be as follows:

load ID_OT,  count ([ID_Intervenant]) as [nb techniciens] resident tab  group by ID_OT;

All fields loaded in the load statement must appear in the group by statement so you can have;

load ID_DI,  count ([ID_Intervenant]) as [nb techniciens] resident tab  group by ID_IT; or

load ID_DI, ID_OT  count ([ID_Intervenant]) as [nb techniciens] resident tab  group by ID_DI, ID_OT; but not

load ID_DI,  count ([ID_Intervenant]) as [nb techniciens] resident tab  group by ID_OT;

If you want to group by ID_OT only then I would suggest the following

Table1:

load

     ID_DI,

     ID_OT,

     [ID_Intervenant]

from x.qvd;

Left Join (Table 1)

Load

     ID_OT,

     Count( [ID_Intervenant]) as [nb techniciens]

from x.qvd

group by ID_OT;

Let me know if you need any more help.

Andy

[rajeshvaswani77]

Your group by should have all the fields except the one you used for aggregation.

table4:

Load ID_OT,

     ID_DI,

     [Descriptif de l'intervention],

     [Code Type Intervention],

     [Code état OT],

     ID_Intervenant,

     [Date création OT],

     [Date début intervention],

     [Date fin intervention],

     [Date Départ Travail],

     [Date retour travail],

Count( ID_Intervenant ) as nb_technicien

resident OT

group by ID_OT,[Descriptif de l'intervention]...... ;

thanks,

Rajesh Vaswani

[michael_maeuser]

if you have a aggregation expression like count, sum etc... you MUST group by all fields that are not in the aggregation!

your example:

table4:

Load ID_OT,

     ID_DI,

     [Descriptif de l'intervention],

     [Code Type Intervention],

     [Code état OT],

     ID_Intervenant,

     [Date création OT],

     [Date début intervention],

     [Date fin intervention],

     [Date Départ Travail],

     [Date retour travail],

Count( ID_Intervenant ) as nb_technicien

resident OT

group by

ID_OT,

     ID_DI,

     [Descriptif de l'intervention],

     [Code Type Intervention],

     [Code état OT],

     ID_Intervenant,

     [Date création OT],

     [Date début intervention],

     [Date fin intervention],

     [Date Départ Travail],

     [Date retour travail],

;

[CELAMBARASAN]

Group By clause has the rule that what ever you specified in the field list without aggregation functions should be included in Group by

So

Load ID_DI,  Count ([ID_Intervenant]) as [nb techniciens] Resident tab  Group By ID_DI;

If you need to use ID_OT you have to include that in field list and also in group by then

Load ID_DI,  ID_OT , Count ([ID_Intervenant]) as [nb techniciens] Resident tab  Group By ID_DI, ID_OT ;

[narender123]

Hi .

Try this.

Load ID_OT,

     ID_DI,

     [Descriptif de l'intervention],

     [Code Type Intervention],

     [Code état OT],

     ID_Intervenant,

     [Date création OT],

     [Date début intervention],

     [Date fin intervention],

     [Date Départ Travail],

     [Date retour travail],

Count( ID_Intervenant ) as nb_technicien

resident OT

group by ID_OT,   ID_DI, [Descriptif de l'intervention],  [Code Type Intervention], [Code état OT], ID_Intervenant,[Date création OT], [Date début intervention], [Date fin intervention], [Date Départ Travail],  [Date retour travail];

Thanks.

Narender

[Anonymous]

correct narender, i am glad you remember the important thing

that is to use all fields , and exact order of source table.

anant

[narender123]

@Anant Dubey

Thanks Boss.