Trying to do this SQL in Qlik

[Anonymous]

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!

[cristian_av]

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);

[Anonymous]

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...

[qv_testing]

Hi,

I think this is store procedure, right ?

if SP,

SET NOCOUNT ON;

Load *,

SQL EXEC SP_Name;

Hope this works..!!

[sasiparupudi1 · Accepted solution]

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;

[Anonymous]

Thanks that solved my issue!