Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all i am wanting to start making some reports that i do in Sql to my company but when i try to migrate the sql query to qlik i have some syntax errors...
This is the query i want to launch...
SQL Declare @Sucursal int, @fecha date;
set @Sucursal = 11;
set @fecha = Getdate() - 1;
select max(FechaRecepcion ) as Fecha, max(so.sucursalnombre) as Sucursalorigen,
e.TipoComprobante as TipoComp,sum(e.subtotal-e.descuento+e.iva+e.seguro) as ImporteTotal,
max(sd.sucursalnombre) as SucursalDestino, iif(e.OrigenPago = 'D','DES','ORG') as OrgPago
from dbo.Envios e
inner join dbo.Sucursales so
on so.sucursalid = e.sucursalidorigen
inner join dbo.Sucursales sd
on sd.sucursalid = e.sucursaliddestino
left join dbo.Clientes co
on e.clienteidorigen = co.clienteid
left join dbo.Clientes cd
on e.clienteiddestino = cd.clienteid
left join dbo.Empleados ef
on e.empleadofacturacion = ef.empleadoid
where Fecharecepcion = @fecha and Anulado = 0 and Tipocomprobante is not null
and SucursalIDEmision < 20 and SucursalIDDestino < 20 and Tipocomprobante IN ('FA', 'FB')
group by so.sucursalnombre, e.TipoComprobante, OrigenPago, e.SucursalIDDestino;
When i run the script it say i need to declare the variable @fecha but either exchanging Getdate() for Now() or Date() it doesnt seems to work...
Wich is the correct syntax??
Thanks so much!
Try
Let @fecha=Date(Floor(Today())-1,'YYYY-MM-DD');
SQL select max(FechaRecepcion ) as Fecha, max(so.sucursalnombre) as Sucursalorigen,
e.TipoComprobante as TipoComp,sum(e.subtotal-e.descuento+e.iva+e.seguro) as ImporteTotal,
max(sd.sucursalnombre) as SucursalDestino, iif(e.OrigenPago = 'D','DES','ORG') as OrgPago
from dbo.Envios e
inner join dbo.Sucursales so
on so.sucursalid = e.sucursalidorigen
inner join dbo.Sucursales sd
on sd.sucursalid = e.sucursaliddestino
left join dbo.Clientes co
on e.clienteidorigen = co.clienteid
left join dbo.Clientes cd
on e.clienteiddestino = cd.clienteid
left join dbo.Empleados ef
on e.empleadofacturacion = ef.empleadoid
where Fecharecepcion ='$(@fecha)' and Anulado = 0 and Tipocomprobante is not null
and SucursalIDEmision < 20 and SucursalIDDestino < 20 and Tipocomprobante IN ('FA', 'FB')
group by so.sucursalnombre, e.TipoComprobante, OrigenPago, e.SucursalIDDestino;
You want to use the now() function of qlikview in SQL? Am I right?
if that, first declare a variable with LET (not SET) to be the Now function i.e.:
Let NowTime= Date(Today(),'DD-MM-YYYY')-1;
Then load it in SQL:
Load *;
SQL Declare
....
set @fecha =$(vAñoInicio);
...
YOUR SQL);
The thing i need to do is that Qlik can run that query because i want to do a report with the data that this query gives me...
Hi,
I think this is store procedure, right ?
if SP,
SET NOCOUNT ON;
Load *,
SQL EXEC SP_Name;
Hope this works..!!
Try
Let @fecha=Date(Floor(Today())-1,'YYYY-MM-DD');
SQL select max(FechaRecepcion ) as Fecha, max(so.sucursalnombre) as Sucursalorigen,
e.TipoComprobante as TipoComp,sum(e.subtotal-e.descuento+e.iva+e.seguro) as ImporteTotal,
max(sd.sucursalnombre) as SucursalDestino, iif(e.OrigenPago = 'D','DES','ORG') as OrgPago
from dbo.Envios e
inner join dbo.Sucursales so
on so.sucursalid = e.sucursalidorigen
inner join dbo.Sucursales sd
on sd.sucursalid = e.sucursaliddestino
left join dbo.Clientes co
on e.clienteidorigen = co.clienteid
left join dbo.Clientes cd
on e.clienteiddestino = cd.clienteid
left join dbo.Empleados ef
on e.empleadofacturacion = ef.empleadoid
where Fecharecepcion ='$(@fecha)' and Anulado = 0 and Tipocomprobante is not null
and SucursalIDEmision < 20 and SucursalIDDestino < 20 and Tipocomprobante IN ('FA', 'FB')
group by so.sucursalnombre, e.TipoComprobante, OrigenPago, e.SucursalIDDestino;
Thanks that solved my issue!