Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!!!!
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.
I'm not using it would have to add so you can do addition, and as is
Can you share complete script for better understanding? I am not finding condition section of if statement proper.
Venta_Detalle:
LOAD Id_Tienda,
Nombre_Tienda,
Id_Transaccion,
Costo,
Cantidad,
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,
Metro_Cuadrado
FROM
[..\QVD\Trans01_Venta_Detalle.qvd]
(qvd);
STORE Venta_Detalle into ..\QVD\Trans02_Venta_Detalle.qvd;
Venta_Detalle:
loAD Id_Tienda,
Nombre_Tienda,
Id_Transaccion,
Costo,
Cantidad,
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,
Metro_Cuadrado
FROM
[..\QVD\Trans01_Venta_Detalle.qvd]
(qvd);
final:
NoConcatenate LOAD Id_Tienda,
Nombre_Tienda,
Id_Transaccion,
Costo,
Cantidad,
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,
Metro_Cuadrado,
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,
Cantidad,
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,
Metro_Cuadrado
STORE Venta_Detalle into ..\QVD\Trans02_Venta_Detalle.qvd;
I try, with what you have stayed put and cycling and the application locked
Hi,
do you have any value to compare with sum(cantidad * costo) condition
if(sum(cantidad * costo)>0,0,sum(Venta_Presupuesto)) as Venta_Presupuesto_New
Regards
It is my net sales, which makes the comparison with sales budget
QV field names are case sensitive, cantidad is not the same as Cantidad, and costo is not the same as Costo.
LOAD ... |
Costo,
Cantidad,
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