Master Calendar with 2 Date Fields

microwin88x — Tue, 04 Feb 2014 03:57:08 GMT

Hello!

I have the following Master Calendar:

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),'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,

MASTER_CALENDAR:

NOCONCATENATE

LOAD DISTINCT *

RESIDENT LK_FECHA_TMP

WHERE EXISTS(FECHA_GESTION,ID_TIE_FECHA)

AND EXISTS(FECHA_GESTION_PAY,ID_TIE_FECHA);

I really need your help with this!

Thank you!!!

Re: Master Calendar with 2 Date Fields

Anonymous — Tue, 04 Feb 2014 04:55:57 GMT

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/ ".

Re: Master Calendar with 2 Date Fields

Anonymous — Tue, 04 Feb 2014 05:46:58 GMT

also the Qlikview Deployment Framework has a very good function to create date dimensions.

QlikView Deployment Framework

topic Re: Master Calendar with 2 Date Fields in QlikView

Master Calendar with 2 Date Fields

Re: Master Calendar with 2 Date Fields

Re: Master Calendar with 2 Date Fields

Re: Master Calendar with 2 Date Fields