Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Filter Dates

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.

Screenshot_3.pngScreenshot_2.png

what can I do??

3 Replies
mwoolf
Honored Contributor II

Re: Filter Dates

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

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

Hi,

maybe one solution could be:

QlikCommunity_Thread_222863_Pic1.JPG

QlikCommunity_Thread_222863_Pic2.JPG

QlikCommunity_Thread_222863_Pic3.JPG

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