Master Calender

[upaliwije]

CLAIM_PAID:

LOAD date(floor(PAID_DATE))as PAID_DATE,BCOD, POLICY_NO,PRD_CODE,CLAIM_NO, PAID_AMOUNT,PAID_RANGE,MONM,CLO_CODE,LEGAL;

sql

select cre_acc_paid_date PAID_DATE ,

       cre_cen_int_branch_code BCOD,

..........

..........

I have above Script in my QV document and below is the Script for Master Calender with Fiscal Year As well

When I load the data the calender does not get connected to my Data model.

Set vFM = 8 ;                                                      

Calendar:

Load Dual(YEAR-1 &'/'& YEAR, YEAR) as FYear,        

         Dual(MONTH, MONTH)                as FMonth,*;

   load        

     Day(PAID_DATE) AS DAY,

    Date(PAID_DATE) AS DATE,

    Week(PAID_DATE) AS WEEK,

   Year(PAID_DATE) AS YEAR,

    Month(PAID_DATE) As MONTH,

    Month(PAID_DATE) &'-'& week(PAID_DATE) As Month_Week;

  Load Date(TRN_MinDate + IterNo() -1 ) AS PAID_DATE While (TRN_MinDate + IterNo() - 1) <= Num(TRN_MaxDate);

   Load

   Min(PAID_DATE) AS TRN_MinDate,

    Max(PAID_DATE) AS TRN_MaxDate  

RESIDENT CLAIM_PAID;

pls let where I have gone wrong and correct it.

[Anonymous · Accepted solution]

Hi,

You should have common field in both the table to link. In your case, PAID_DATE should be part of calendar table,

Calendar:

Load Dual(YEAR-1 &'/'& YEAR, YEAR) as FYear,      

         Dual(MONTH, MONTH)                as FMonth,*;

   load

   PAID_DATE,      

     Day(PAID_DATE) AS DAY,

    Date(PAID_DATE) AS DATE,

    Week(PAID_DATE) AS WEEK,

   Year(PAID_DATE) AS YEAR,

    Month(PAID_DATE) As MONTH,

    Month(PAID_DATE) &'-'& week(PAID_DATE) As Month_Week;

  Load Date(TRN_MinDate + IterNo() -1 ) AS PAID_DATE While (TRN_MinDate + IterNo() - 1) <= Num(TRN_MaxDate);

   Load

   Min(PAID_DATE) AS TRN_MinDate,

    Max(PAID_DATE) AS TRN_MaxDate

RESIDENT CLAIM_PAID;