topic Re: Filter Dates in QlikView

Filter Dates

Tue, 28 Jun 2016 17:08:33 GMT

Hi!

I have a field with a full format date.

Table1_xls:
LOAD
     DATE#(DATE(Fecha,'DD/MM/YYYY')&' '&TIME(Hora,'hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss TT') AS FechaHora,
...

then I create fields with diferent date format.

Table2_Qvd:
LOAD  
     FechaHora,
     Date(FechaHora,'MM/YYYY') AS MesAño,
     Date(FechaHora,'DD/MM') AS DiaMes,
     Date(FechaHora,'DD-hh TT') AS HoraDia,
     Year(FechaHora) AS Año,
     Month(FechaHora) AS Mes,
     Day(FechaHora) AS Día,
...

but these fields are kept separate and not grouped by common dates.

what can I do??

Re: Filter Dates

m_woolf — Tue, 28 Jun 2016 17:59:44 GMT

Try:

Date(Floor(FechaHora),'MM/YYYY') AS MesAño,

Date(Floor(FechaHora),'DD/MM') AS DiaMes,

Date(Floor(FechaHora),'DD-hh TT') AS HoraDia,

Re: Filter Dates

sunny_talwar — Tue, 28 Jun 2016 19:59:47 GMT

May be this:

Table2_Qvd:

LOAD

FechaHora,

Date(MonthStart(FechaHora),'MM/YYYY') AS MesAño,

Date(MonthStart(FechaHora),'DD/MM') AS DiaMes,

Date(FechaHora,'DD-hh TT') AS HoraDia,

Year(FechaHora) AS Año,

Month(FechaHora) AS Mes,

Day(FechaHora) AS Día,

Re: Filter Dates

MarcoWedel — Tue, 28 Jun 2016 21:54:17 GMT

Hi,

maybe one solution could be:

LOAD *,  
     Date(MonthName(Fecha),'MM/YYYY') as MesAño,  
     Date(SetDateYear(Fecha,2000),'DD/MM') as DiaMes,  
     Date(Date#(Date(FechaHora,'DD-hh'),'DD-hh'),'DD-hh TT') as HoraDia,
     Year(FechaHora) as Año,  
     Month(FechaHora) as Mes,  
     Day(FechaHora) as Día;
LOAD Fecha,
     Hora,
     Date(Fecha+Hora,'DD/MM/YYYY hh:mm:ss TT') as FechaHora;
     
// sample data generation
LOAD Date(MakeDate(2015)+RecNo()-1) as Fecha,
     Time(Rand()) as Hora
AutoGenerate 731;

hope this helps

regards

Marco