topic Re: sum or group by in QlikView

sum or group by

Thu, 07 Mar 2013 08:08:25 GMT

I have values in a column in a table QTE and I want to display the values as they present themselves in the column. When I use the values SUM (QTY) are not correct. These data are from a SQL SERVER. and I thought I should do a group by but I can not do

Carburant_Analyse:

LOAD

Application as APPLICATION,

"TYPE_PRODUIT",

ANALYTIQUE,

left(ANALYTIQUE,7) as COMPTE_ANALYTIQUE,

"Type_Vehicule" as TYPE_VEHICULE,

ANNEE,

Mois,

"NUM_AP5",

QTE,

MONTANT,

Compteur,

"CONSO_MOYENNE",

"NBRE_PRISE",

DATE,

anomalie,

TypeCompteur,

if(Application='TOTAL',QTE) as QTE_TOTAL,

if(Application='LVNET',QTE) as QTE_LVNET,

if(Application='LVNET',Compteur) as COMPTEUR_LVNET,

if(Application='LVNET',"CONSO_MOYENNE") as CONSO_MOY_LVNET,

if(Application='LVNET',"NBRE_PRISE") as NB_PRISE_LVNET,

if(Application='GESPARC',QTE) as QTE_GESPARC,

if(Application='GESPARC',Compteur) as COMPTEUR_GESPARC,

if(Application='GESPARC',"CONSO_MOYENNE") as CONSO_MOY_GESPARC,

if(Application='GESPARC',"NBRE_PRISE") as NB_PRISE_GESPARC,

if(Application='Comptabilité' and TYPE_PRODUIT='Carburants',QTE) as QTE_SOMILOG,

if(Application='Comptabilité' and TYPE_PRODUIT='Carburants',Compteur) as COMPTEUR_SOMILOG,

if(Application='Comptabilité' and TYPE_PRODUIT='Carburants',"CONSO_MOYENNE") as CONSO_MOY_SOMILOG,

if(Application='Comptabilité' and TYPE_PRODUIT='Carburants',"NBRE_PRISE") as NB_PRISE_SOMILOG;

SQL SELECT * FROM "Reporting_SucafCi".dbo."CARBURANT_ANALYSE";

In simple tables I was forced to sum distinct but results are accurate when there is a single value but false when there are duplicate values

I need help

Re: sum or group by

Thu, 07 Mar 2013 11:35:58 GMT

LOAD

Application as APPLICATION,

"TYPE_PRODUIT",

ANALYTIQUE,
"Type_Vehicule" as TYPE_VEHICULE,

ANNEE,

Mois,

"NUM_AP5",

QTE,

MONTANT,

Compteur,

"CONSO_MOYENNE",

"NBRE_PRISE",

DATE,

anomalie,

TypeCompteur

Group By

Application,

"TYPE_PRODUIT",

ANALYTIQUE,

"Type_Vehicule",

ANNEE,

Mois,

"NUM_AP5",

QTE,

MONTANT,

Compteur,

"CONSO_MOYENNE",

"NBRE_PRISE",

DATE,

anomalie,

TypeCompteur ;

SQL

SELECT * FROM "Reporting_SucafCi".dbo."CARBURANT_ANALYSE";

when i reload i have this message

Aggregation expressions required by GROUP BY clause

Re: sum or group by

Thu, 07 Mar 2013 11:43:48 GMT

LOAD

Application as APPLICATION,

"TYPE_PRODUIT",

ANALYTIQUE,
"Type_Vehicule" as TYPE_VEHICULE,

ANNEE,

Mois,

"NUM_AP5",

QTE,

MONTANT,

Compteur,

"CONSO_MOYENNE",

"NBRE_PRISE",

DATE,

anomalie,

TypeCompteur

Group By

Application,

"TYPE_PRODUIT",

ANALYTIQUE,

"Type_Vehicule",

ANNEE,

Mois,

"NUM_AP5",

QTE,

MONTANT,

Compteur,

"CONSO_MOYENNE",

"NBRE_PRISE",

DATE,

anomalie,

TypeCompteur ;

SQL

SELECT * FROM "Reporting_SucafCi".dbo."CARBURANT_ANALYSE";

when i reload i have this message

Aggregation expressions required by GROUP BY clausev