DATE( MAKEDATE(ID_TIE_AÑO,ID_TIE_MES,ID_TIE_DIA),'YYYYMMDD') AS KEY_PERIODO,
DATE( MAKEDATE(ID_TIE_AÑO,ID_TIE_MES),'YYYYMM') AS ID_TIE_AÑOMES,
DATE( MAKEDATE(ID_TIE_AÑO,ID_TIE_MES),'MMM-YY') AS ID_TIE_AÑOMESDESC,
DATE( MAKEDATE(ID_TIE_AÑO,ID_TIE_MES,ID_TIE_DIA),'YYYYMMDD') AS ID_TIE_AÑOMESDIA,
'Q'&ceil(ID_TIE_MES/3) AS ID_TIE_TRIMESTRE;
LOAD ID_TIE_FECHA,
YEAR(ID_TIE_FECHA) AS ID_TIE_AÑO,
MONTH(ID_TIE_FECHA) AS ID_TIE_MES,
NUM(MONTH(ID_TIE_FECHA)) AS ID_TIE_MES#,
NUM(DAY(ID_TIE_FECHA)) AS ID_TIE_DIA,
WEEKDAY(ID_TIE_FECHA) AS ID_TIE_DIA_SEMANA,
NUM(ID_TIE_FECHA) AS ID_TIE_FECHA#;
LOAD
DATE(MAKEDATE(2007,1,1)+(ITERNO()-1),'YYYYMMDD') AS ID_TIE_FECHA
AUTOGENERATE 1
WHILE DATE(MAKEDATE(2007,1,1)+(ITERNO()-1)) <= DATE(MAKEDATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())));
MASTER_CALENDAR:
NOCONCATENATE
LOAD DISTINCT
*
RESIDENT LK_FECHA_TMP
WHERE EXISTS(FECHA_GESTION,ID_TIE_FECHA);
DROP FIELD FECHA_GESTION;
DROP TABLE LK_FECHA_TMP;
Then I have my transactional table with all the information related to the Tasks.
The Master Calendar is asociated to the Tasks table by the "Complete Date":
TASKS:
Field1,
Field2,
Field3,
DATE (FLOOR (COMPLETE_DTTM), 'YYYYMMDD')
as KEY_PERIODO,
DATE (FLOOR (COMPLETE_DTTM), 'YYYYMMDD')
as FECHA_GESTION
Everything works okay so far, but the thing is that now I have another table, Pays, which has a field named "Pay Date" and Amounts, and I'd like to use the same Master Calendar to see my information by both, the "Complete Date" and also by the "Pay Date" if I select a particular date, to visualize the associated data. How could I do that? Should I add the following?
PAYS:
Field1_Pay,
Field2_Pay,
Field3_Pay,
Amounts,
DATE (FLOOR (PAY_DT), 'YYYYMMDD')
as KEY_PERIODO_PAY,
DATE (FLOOR (PAY_DT), 'YYYYMMDD')
as FECHA_GESTION_PAY
But then how could I modify the Master Calendar code? It would work like this?
LK_DATE_TMP:
LOAD *,
DATE( MAKEDATE(ID_TIE_AÑO,ID_TIE_MES,ID_TIE_DIA),'YYYYMMDD') AS KEY_PERIODO,
DATE( MAKEDATE(ID_TIE_AÑO,ID_TIE_MES,ID_TIE_DIA),'YYYYMMDD') AS KEY_PERIODO_PAY,
If I have to deal with multiple date fields I typically make a Master Calendar for each date field. You can speed up this process by using an already defined Sub Function that builds the Calendar for you. You can find a very good one here " code.google.com/p/qlikview-components/ ".
