topic Erreur Group by in QlikView

Erreur Group by

Tue, 17 Feb 2015 15:22:47 GMT

Erreur lorsque j'ai ajouter max et sum dans le script ci dessous:

VL:

LOAD

CUSTOMER_TRX_ID as [VenteLigne. Pièce_ID],

INVENTORY_ITEM_ID as [VenteLigne. Article ID],

DESCRIPTION as [VenteLigne. Description Article],

if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED) as [VenteLigne. Qté],

(UNIT_STANDARD_PRICE) as [VenteLigne. Prix_Theorique],

UNIT_SELLING_PRICE as [VenteLigne. Prix_de_Vente],

max((UNIT_STANDARD_PRICE))-sum(UNIT_SELLING_PRICE)*(if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED)) as [VenteLigne. Remise],

UNIT_STANDARD_PRICE*(if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED)) as [VenteLigne. Montant Brut],

if(INTERFACE_LINE_CONTEXT='INTERCOMPANY','Groupe','Hors Groupe') as [VenteLigne. Context],

INTERFACE_LINE_ATTRIBUTE1 as [VenteLigne. Num Cde],

EXTENDED_AMOUNT as [VenteLigne. Montant],

ORG_ID as [VenteLigne. SociétéID]

FROM

[..\QVD_Sources\RA_CUSTOMER_TRX_LINES_ALL.QVD]

(qvd)

Where

ORG_ID='155'

//or ORG_ID='158'

and (DESCRIPTION<>'Timbre Fiscal' and DESCRIPTION<>'Report')

and LINE_TYPE='LINE'

and (INTERFACE_LINE_CONTEXT='ORDER ENTRY' or INTERFACE_LINE_CONTEXT='INTERCOMPANY')

group by CUSTOMER_TRX_ID,INVENTORY_ITEM_ID

;

Re: Erreur Group by

rubenmarin — Tue, 17 Feb 2015 15:26:08 GMT

Hi Brahim, if you're using aggregation opetaions like Sum or Max you need to add to the 'group by' clause every field that is not aggregated (ORG_ID, EXTENDED_AMOUNT...)

Re: Erreur Group by

Tue, 17 Feb 2015 15:44:17 GMT

meme problème lorque j'ai ajouter tous les champs sauf UNIT_STANDARD_PRICE etUNIT_SELLING_PRICE

Re: Erreur Group by

rubenmarin — Tue, 17 Feb 2015 15:55:39 GMT

Hi Brahim, there is also this line:

UNIT_STANDARD_PRICE*(if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED)) as [VenteLigne. Montant Brut],

UNIT_STANDARD_PRICE isn't in a aggregation function, can you put this field in the group by to check if this is the origin of the problem? Knowing that we can isolate the problem and work in the solution.

Also, you sure of this line?: max((UNIT_STANDARD_PRICE))-sum(UNIT_SELLING_PRICE)...

The Max value of UNIT_STANDARD_PRICE substracted by the sum of every value in UNIT_SELLING_PRICE?

Re: Erreur Group by

Tue, 17 Feb 2015 16:29:08 GMT

meme pb :

VL:

LOAD

CUSTOMER_TRX_ID as [VenteLigne. Pièce_ID],

INVENTORY_ITEM_ID as [VenteLigne. Article ID],

DESCRIPTION as [VenteLigne. Description Article],

if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED) as [VenteLigne. Qté],

(UNIT_STANDARD_PRICE) as [VenteLigne. Prix_Theorique],

UNIT_SELLING_PRICE as [VenteLigne. Prix_de_Vente],

max((UNIT_STANDARD_PRICE))-sum(UNIT_SELLING_PRICE)*(if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED)) as [VenteLigne. Remise],

UNIT_STANDARD_PRICE*(if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED)) as [VenteLigne. Montant Brut],

if(INTERFACE_LINE_CONTEXT='INTERCOMPANY','Groupe','Hors Groupe') as [VenteLigne. Context],

INTERFACE_LINE_ATTRIBUTE1 as [VenteLigne. Num Cde],

EXTENDED_AMOUNT as [VenteLigne. Montant],

ORG_ID as [VenteLigne. SociétéID]

FROM

[..\QVD_Sources\RA_CUSTOMER_TRX_LINES_ALL.QVD]

(qvd)

Where

ORG_ID='155'

//or ORG_ID='158'

and (DESCRIPTION<>'Timbre Fiscal' and DESCRIPTION<>'Report')

and LINE_TYPE='LINE'

and (INTERFACE_LINE_CONTEXT='ORDER ENTRY' or INTERFACE_LINE_CONTEXT='INTERCOMPANY')

group by CUSTOMER_TRX_ID,

INVENTORY_ITEM_ID,

DESCRIPTION,

INTERFACE_LINE_CONTEXT,

INTERFACE_LINE_ATTRIBUTE1,

ORG_ID,

EXTENDED_AMOUNT,

if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED),

UNIT_STANDARD_PRICE*(if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED)) as [VenteLigne. Montant Brut]

;

Re: Erreur Group by

rubenmarin — Tue, 17 Feb 2015 16:35:13 GMT

Hi Brahim, in group by should be only field names, not expressions:

LOAD .....

FROM

[..\QVD_Sources\RA_CUSTOMER_TRX_LINES_ALL.QVD]

(qvd)

Where

ORG_ID='155'

//or ORG_ID='158'

and (DESCRIPTION<>'Timbre Fiscal' and DESCRIPTION<>'Report')

and LINE_TYPE='LINE'

and (INTERFACE_LINE_CONTEXT='ORDER ENTRY' or INTERFACE_LINE_CONTEXT='INTERCOMPANY')

group by CUSTOMER_TRX_ID,

INVENTORY_ITEM_ID,

DESCRIPTION,

INTERFACE_LINE_CONTEXT,

INTERFACE_LINE_ATTRIBUTE1,

ORG_ID,

EXTENDED_AMOUNT,

QUANTITY_CREDITED,

QUANTITY_INVOICED,

UNIT_STANDARD_PRICE,

UNIT_SELLING_PRICE;

Again, as you're using aggregated and not aggregated UNIT_STANDARD_PRICE and UNIT_SELLING_PRICE this don't make sense, but at least you can know the cause of the error.