10 Replies Latest reply: Sep 22, 2015 10:32 AM by josue zurita

Enter the following query in the scrip

It does not allow me to enter the following expression in the scrip, send me an error, saying, field not found.

if(sum(cantidad * costo), sum(Venta_Presupuesto) = 0,sum(Venta_Presupuesto)) as Venta_Presupuesto

Help!!!!!!

• Re: Enter the following query in the scrip

Are you using a Group By Statement or not? Because you are using a aggregation (in the form of Sum) you need Group By statement in your script here.

• Re: Enter the following query in the scrip

I'm not using it would have to add so you can do addition, and as is

• Re: Enter the following query in the scrip

Can you share complete script for better understanding? I am not finding condition section of if statement proper.

• Re: Enter the following query in the scrip

Venta_Detalle:

Nombre_Tienda,

Id_Transaccion,

Costo,

Fecha_Transaccion,

left(month(Fecha_Transaccion),3)    as Mes,

num(month(Fecha_Transaccion)) as Num_Mes,

If(left(month(Fecha_Transaccion),3) = 'Ene', '1-Ene', If(left(month(Fecha_Transaccion),3) = 'Feb', '2-Feb', If(left(month(Fecha_Transaccion),3) = 'Mar', '3-Mar',If(left(month(Fecha_Transaccion),3) = 'Abr', '4-Abr',

If(left(month(Fecha_Transaccion),3) = 'May', '5-May',If(left(month(Fecha_Transaccion),3) = 'Jun', '6-Jun',If(left(month(Fecha_Transaccion),3) = 'Jul', '7-Jul',If(left(month(Fecha_Transaccion),3) = 'Ago', '8-Ago',

If(left(month(Fecha_Transaccion),3) = 'Sep', '9-Sep',If(left(month(Fecha_Transaccion),3) = 'Oct', '10-Oct',If(left(month(Fecha_Transaccion),3) = 'Nov', '11-Nov',If(left(month(Fecha_Transaccion),3) = 'Dic', '12-Dic'))))))))))))    as Meses,

Week(Fecha_Transaccion)    as Semana,

//Dia,

NumDia,

Hora_Transaccion,

//Fecha,

date(Fecha)    as Fecha,

left(WeekDay(Date(Fecha)),3) as Dia,

If(Dia_Semana ='2','1-Lun',If(Dia_Semana ='3','2-Mar',If(Dia_Semana ='4','3-Mie',If(Dia_Semana ='5','4-Jue',If(Dia_Semana ='6','5-Vie',If(Dia_Semana ='7','6-Sab',If(Dia_Semana ='1','7-Dom'))))))) as Dias,

Trimestre,

Semestre,

Cuatrimestre,

Año,

//Mes,

DiaSemana,

Dia_Semana,

Semana_Retail,

TIME,

CantTicket,

Transacciones_Presupuesto,

Venta_Presupuesto,

//if(sum(cantidad * costo), sum(Venta_Presupuesto) = 0,sum(Venta_Presupuesto)) as Venta_Presupuesto,

FROM

[..\QVD\Trans01_Venta_Detalle.qvd]

(qvd);

STORE Venta_Detalle into ..\QVD\Trans02_Venta_Detalle.qvd;

• Re: Enter the following query in the scrip

Venta_Detalle:

Nombre_Tienda,

Id_Transaccion,

Costo,

Fecha_Transaccion,

left(month(Fecha_Transaccion),3)    as Mes,

num(month(Fecha_Transaccion)) as Num_Mes,

If(left(month(Fecha_Transaccion),3) = 'Ene', '1-Ene', If(left(month(Fecha_Transaccion),3) = 'Feb', '2-Feb', If(left(month(Fecha_Transaccion),3) = 'Mar', '3-Mar',If(left(month(Fecha_Transaccion),3) = 'Abr', '4-Abr',

If(left(month(Fecha_Transaccion),3) = 'May', '5-May',If(left(month(Fecha_Transaccion),3) = 'Jun', '6-Jun',If(left(month(Fecha_Transaccion),3) = 'Jul', '7-Jul',If(left(month(Fecha_Transaccion),3) = 'Ago', '8-Ago',

If(left(month(Fecha_Transaccion),3) = 'Sep', '9-Sep',If(left(month(Fecha_Transaccion),3) = 'Oct', '10-Oct',If(left(month(Fecha_Transaccion),3) = 'Nov', '11-Nov',If(left(month(Fecha_Transaccion),3) = 'Dic', '12-Dic'))))))))))))    as Meses,

Week(Fecha_Transaccion)    as Semana,

//Dia,

NumDia,

Hora_Transaccion,

//Fecha,

date(Fecha)    as Fecha,

left(WeekDay(Date(Fecha)),3) as Dia,

If(Dia_Semana ='2','1-Lun',If(Dia_Semana ='3','2-Mar',If(Dia_Semana ='4','3-Mie',If(Dia_Semana ='5','4-Jue',If(Dia_Semana ='6','5-Vie',If(Dia_Semana ='7','6-Sab',If(Dia_Semana ='1','7-Dom'))))))) as Dias,

Trimestre,

Semestre,

Cuatrimestre,

Año,

//Mes,

DiaSemana,

Dia_Semana,

Semana_Retail,

TIME,

CantTicket,

Transacciones_Presupuesto,

Venta_Presupuesto,

//if(sum(cantidad * costo), sum(Venta_Presupuesto) = 0,sum(Venta_Presupuesto)) as Venta_Presupuesto,

FROM

[..\QVD\Trans01_Venta_Detalle.qvd]

(qvd);

final:

Nombre_Tienda,

Id_Transaccion,

Costo,

Fecha_Transaccion,

Mes,

Num_Mes,

Meses,

Semana,

NumDia,

Hora_Transaccion,

Fecha,

Dia,

Dias,

Trimestre,

Semestre,

Cuatrimestre,

Año,

DiaSemana,

Dia_Semana,

Semana_Retail,

TIME,

CantTicket,

Transacciones_Presupuesto,

Venta_Presupuesto,

if(sum(cantidad * costo), sum(Venta_Presupuesto) = 0,sum(Venta_Presupuesto)) as Venta_Presupuesto

Resident Venta_Detalle

group by Id_Tienda,

Nombre_Tienda,

Id_Transaccion,

Costo,

Fecha_Transaccion,

Mes,

Num_Mes,

Meses,

Semana,

NumDia,

Hora_Transaccion,

Fecha,

Dia,

Dias,

Trimestre,

Semestre,

Cuatrimestre,

Año,

DiaSemana,

Dia_Semana,

Semana_Retail,

TIME,

CantTicket,

Transacciones_Presupuesto,

Venta_Presupuesto,

STORE Venta_Detalle into ..\QVD\Trans02_Venta_Detalle.qvd;

• Re: Enter the following query in the scrip

I try, with what you have stayed put and cycling and the application locked

• Re: Enter the following query in the scrip

Hi,

do you have any value to compare with sum(cantidad * costo) condition

Regards

• Re: Enter the following query in the scrip

It is my net sales, which makes the comparison with sales budget

• Re: Enter the following query in the scrip

QV field names are case sensitive, cantidad  is not the same as  Cantidad, and costo is not the same as Costo.

Costo,

 if(sum(cantidad * costo), sum(Venta_Presupuesto) = 0,sum(Venta_Presupuesto)) as Venta_Presupuesto,

...

And you need a Group By that contains ALL fields not inside an aggregation function.

And you cannot have a field both inside and outside an aggregation function

• Re: Enter the following query in the scrip

correct letters are capitalized and also add a group, but even so I send error message