Script doesn´t work as should

Tue, 30 Jul 2013 17:01:37 GMT

Hi all,

I have a script that works many days ago, but now doesnt.

OLEDB CONNECT TO .......;

Cabeceras_Temp:

NoConcatenate

Load

[Codi_local]

,[Servicio]

,[Nume_docum]

,[Codi_clien]

,[Codi_emple]

,[Nume_perso]

,[Año]

,[Mes]

,date(Fech_docum,'MM/DD/YYYY') as [Fech_docum]

,[Semanas]

,[Periodos]

,[Año_periodo]

,[Weekpart]

,[Daypart]

,[Hora]

,[Tipo_pagos]

,[Tota_docum]

,[Dcto_docum]

,[Tiva_docum]

,[Tser_docum]

,[Reca_docum]

,[Cobr_docum]

,[Esta_docum]

,[Cant_promo]

,[Neto_docum]

;

SELECT [Codi_local]

,CASE WHEN codi_docum = 'R' THEN 'Restaurante' WHEN codi_docum IN ('L', 'S') THEN 'Llevar' WHEN codi_docum = 'E' THEN 'Express' WHEN codi_docum IN ('D', 'C') THEN 'Domicilio' END AS [Servicio]

,[Nume_docum]

,[Codi_clien]

,[Codi_emple]

,[Nume_perso]

,DATEPART(YEAR, Fech_docum) AS [Año]

,DATEPART(M, Fech_docum) AS [Mes]

,[Fech_docum]

,[Semanas]

,[Periodos]

,[Año_periodo]

,CASE WHEN DATENAME(DW, Fech_docum) IN ('Lunes', 'Martes', 'Miércoles', 'Jueves') THEN 'Weekday' ELSE 'Weekend' END AS [Weekpart]

,[Daypart]

,round([Hora_docum],0,1) as [Hora]

,[Tipo_pagos]

,[Tota_docum]

,[Dcto_docum]

,[Tiva_docum]

,[Tser_docum]

,[Reca_docum]

,[Cobr_docum]

,[Esta_docum]

,[Cant_promo]

,[Neto_docum]

FROM [Sodetur_PH_BI].[Express].[Moc_docu]

JOIN [REPORTES].Parametros_daypart AS parametros

ON ( (round(hora_docum,0,1) between parametros.HORA_INI AND parametros.HORA_FIN))

JOIN Reportes.Parametros_periodos periodos

on ( Fech_docum between periodos.fecha_inicio AND periodos.fecha_fin)

where codi_docum in('S','L','E','D','C','R')

and Esta_docum='C' and Fech_docum > $(vUltFecha);

the strange part is when i delete "and Fech_docum > $(vUltFecha)" the script runs fine, but i need this filter. I take the script part on debugg mode when the variable is set with date, and sql server give me the set for the query, then i replace the variable with '07/24/2013', into qlikview still not working, but sql server give me the set again.

I haven´t idea about whats wrong or what happen. Someone can help me?

Thanks in advance,

Santiago.

Re: Script doesn´t work as should

tresB — Tue, 30 Jul 2013 17:06:48 GMT

seems, it's a date format issue. check the date format of Fech_docum at sql server and make the variable hold the similar format date, it should work then.

Re: Script doesn´t work as should

Tue, 30 Jul 2013 17:22:34 GMT

You´re right, well, what i did was change 07/24/2013 into 24/07/2013 and ran fine, then i add a set format for variable

LET

vUltFecha = date(Peek('FechaMaxima', 0),'DD/MM/YYYY');

Thanks,

Santiago.

topic Re: Script doesn´t work as should in QlikView

Script doesn´t work as should

Re: Script doesn´t work as should

Re: Script doesn´t work as should