topic Re: Trying to do this SQL in Qlik in QlikView

Trying to do this SQL in Qlik

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Trying to do this SQL in Qlik

cristian_av — Fri, 09 Mar 2018 17:58:36 GMT

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

Re: Trying to do this SQL in Qlik

Anonymous — Fri, 09 Mar 2018 18:56:00 GMT

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

Re: Trying to do this SQL in Qlik

Nagaraju_KCS — Mon, 12 Mar 2018 07:21:34 GMT

Hi,

I think this is store procedure, right ?

if SP,

SET NOCOUNT ON;

Load *,

SQL EXEC SP_Name;

Hope this works..!!

Re: Trying to do this SQL in Qlik

sasiparupudi1 — Mon, 12 Mar 2018 07:43:26 GMT

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;

Re: Trying to do this SQL in Qlik

Anonymous — Mon, 12 Mar 2018 12:27:50 GMT

Thanks that solved my issue!