topic Re: Date format are different in QlikView

Date format are different

EternalDX — Sat, 16 Nov 2024 04:44:36 GMT

Hi, im new in QlikView and in make a board to integrate data from different databases and all well fine, but when i try to see this values in a chart some data is not there and found a strange problem with dates and i don't know how to fix it, i attached a image

Regards

Isaias

Re: Date format are different

dplr-rn — Wed, 16 Jan 2019 14:16:29 GMT

In you script where you are loading Fetcha. change it to something like below
Date(Fetcha, 'YYYY-MM-DD') as Fetcha,

Re: Date format are different

EternalDX — Wed, 16 Jan 2019 14:18:54 GMT

Hi Dilipranjith,

I have dealt with that but it does the same

Re: Date format are different

dplr-rn — Wed, 16 Jan 2019 14:20:00 GMT

Whats the data type for the field in the data model?

Re: Date format are different

EternalDX — Wed, 16 Jan 2019 14:27:44 GMT

I think it's a subject of the databases, in both the data type is date but the collage are different in one is Modern_Spanish_CI_AS and the other isLatin1_General_CI_AS, could it be that? and if so, how do I fix it?

Re: Date format are different

dplr-rn — Wed, 16 Jan 2019 14:33:17 GMT

You have to fix it when you load it.
Maybe try combination of date(date#(field, 'DBDATEFormat'),'YouDesiredDATEFORMAT')

Re: Date format are different

marcus_sommer — Wed, 16 Jan 2019 14:38:27 GMT

Without any measures on the fields by loading the data will be a date only interpreted as date if it has the format as in the default-variables (at the beginning of the script). This means you need to convert and format all date-fields which are different to it. This could be done with: date(date#(DATE, 'FormatString')) and if there are multiple different formattings within a single field maybe with: alt(date(date#(DATE, 'FormatString1')), date(date#(DATE, 'FormatString2'))).

- Marcus

Re: Date format are different

pradosh_thakur — Wed, 16 Jan 2019 14:40:52 GMT

the upper dates are in string so use date(date#(Fecha,'YYYY-MM-DD'),'DD/MM/YYYY')

If you have diffrent formats try

alt(your expression,date(date#(Fecha,'YYYY-MM-DD'),'DD/MM/YYYY')) and it shall fetch all the dates properly

Re: Date format are different

EternalDX — Wed, 16 Jan 2019 14:58:16 GMT

Hi to all,

I will try your solutions but the result are the same, check the picture

Re: Date format are different

pradosh_thakur — Wed, 16 Jan 2019 15:01:08 GMT

alt(date(date#(Fecha,'DD/MM/YYYY'),'DD/MM/YYYY'),date(date#(Fecha,'YYYY-MM-DD'),'DD/MM/YYYY'))

Re: Date format are different

EternalDX — Wed, 16 Jan 2019 15:04:20 GMT

I'm sorry, it gives me the same result a '-'

Re: Date format are different

pradosh_thakur — Wed, 16 Jan 2019 15:11:56 GMT

Please attach screenshot of the source data

Re: Date format are different

EternalDX — Wed, 16 Jan 2019 15:20:18 GMT

This is the source

And this is the load script:

Indicadores:
Load
Bloque
,Año
,Mes
,Dia
,alt(date(date#(Fecha,'DD/MM/YYYY'),'DD/MM/YYYY'),date(date#(Fecha,'YYYY-MM-DD'),'DD/MM/YYYY')) As Fecha
,[PotencialBruta]
,[PotencialNeta]
,[PotencialAgua]
,[PotencialGas]
,PotencialCondensado
,[DiferidaBruto]
,[DiferidaNeta]
,[DiferidaAgua]
,[DiferidaGas]
,DiferidaCondensado
,[HorasDiferida]
,[BombeoBruta]
,[BombeoNeto]
,[BombeoAgua]
,BombeoCondensado
,[PozosActivos]
,[PozosInactivos]
,[PozosTempInactivos]
,Gas
,[Produccion]
,[ProduccionNeta]
,[ProduccionAgua]
,ProduccionGas
,[Existencias];

SQL SELECT [Bloque]
,Year(Fecha) As Año
,Month(Fecha) As Mes
,Day(Fecha) As Dia
, Fecha
,[PotencialBruta]
,[PotencialNeta]
,[PotencialAgua]
,[PotencialGas]
,[PotencialCondensado]
,[DiferidaBruto]
,[DiferidaNeta]
,[DiferidaAgua]
,[DiferidaGas]
,[DiferidaCondensado]
,[HorasDiferida]
,[BombeoBruta]
,[BombeoNeto]
,[BombeoAgua]
,[BombeoCondensado]
,[PozosActivos]
,[PozosInactivos]
,[PozosTempInactivos]
,[Gas]
,[Produccion]
,[ProduccionNeta]
,[ProduccionAgua]
,[ProduccionGas]
,[Existencias]
FROM [dbo].[t_01_Indicadores];

Indicadores2:
Load
Bloque
,Año
,Mes
,Dia
,alt(date(date#(Fecha,'DD/MM/YYYY'),'DD/MM/YYYY'),date(date#(Fecha,'YYYY-MM-DD'),'DD/MM/YYYY')) As Fecha
,[PotencialBruta]
,[PotencialNeta]
,[PotencialAgua]
,[PotencialGas]
,PotencialCondensado
,[DiferidaBruto]
,[DiferidaNeta]
,[DiferidaAgua]
,[DiferidaGas]
,DiferidaCondensado
,[HorasDiferida]
,[BombeoBruta]
,[BombeoNeto]
,[BombeoAgua]
,BombeoCondensado
,[PozosActivos]
,[PozosInactivos]
,[PozosTempInactivos]
,Gas
,[Produccion]
,[ProduccionNeta]
,[ProduccionAgua]
,ProduccionGas
,[Existencias];
SQL SELECT 0 As Bloque
,Year(Fecha) As Año
,Month(Fecha) As Mes
,Day(Fecha) As Dia
,Fecha
,[PotencialBruta]
,[PotencialNeta]
,[PotencialAgua]
,[PotencialGas]
, 0 As PotencialCondensado
,[DiferidaBruto]
,[DiferidaNeta]
,[DiferidaAgua]
,[DiferidaGas]
,0 As DiferidaCondensado
,[HorasDiferida]
,[BombeoBruta]
,[BombeoNeto]
,[BombeoAgua]
,0 As BombeoCondensado
,[PozosActivos]
,[PozosInactivos]
,[PozosTempInactivos]
,[PGMMPCD] As Gas
,[Produccion]
,[ProduccionNeta]
,[ProduccionAgua]
,0 As ProduccionGas
,[Existencias]
FROM [dbo].[v_qv_Indicadores];

Re: Date format are different

EternalDX — Wed, 16 Jan 2019 18:04:05 GMT

Thanks to all, i resolve this with the function MakeDate