Max Date and Calendar in Load script

[cbaqir]

I need to take the MAX(TGT_PROD_RELEASE_DATE) for each DEFECT_ID and then create a calendar form it so that I can create list boxes for Year, Month, Quarter, MonthStart etc... I  think I'm close but it keeps failing. Any help is appreciated.

UNQUALIFY *;
FACT_ALM_DEFECT_TGT_PROD_RELEASE_DATE:
LOAD  "DEFECT_ID",
date("TGT_PROD_RELEASE_DATE",'MM/DD/YYYY') as TGT_PROD_RELEASE_DATE;
SQL SELECT *
FROM "IT_DW".dbo."FACT_ALM_DEFECT_TGT_PROD_RELEASE_DATE";

T1:
Load distinct
DEFECT_ID,
max(date(TGT_PROD_RELEASE_DATE,'MM/DD/YYYY')) as MAX_TGT_PROD_RELEASE_DATE
  Resident FACT_ALM_DEFECT_TGT_PROD_RELEASE_DATE Group by DEFECT_ID;
  DROP Table FACT_ALM_DEFECT_TGT_PROD_RELEASE_DATE;

UNQUALIFY *;
TGT_PROD_MasterCalendar:
  LOAD date(MAX_TGT_PROD_RELEASE_DATE, 'MM/DD/YYYY') as TGT_PROD_RELEASE_DATE,
  date(MAX_TGT_PROD_RELEASE_DATE, 'MMMYYYY') as TP_MY,
  Year(MAX_TGT_PROD_RELEASE_DATE) as TP_Year,
  Month(MAX_TGT_PROD_RELEASE_DATE) as TP_Month,
  QuarterStart(MAX_TGT_PROD_RELEASE_DATE) as TP_QTR,
  Day(MAX_TGT_PROD_RELEASE_DATE) as TP_Day;
  LOAD DISTINCT MAX_TGT_PROD_RELEASE_DATE
Resident T1;

[jwjackso]

I created an inline table with a few rows of data for the initial data.  Your script seemed to run fine.  Are you sure the TGT_PROD_RELEASE_DATE is in the correct format?

[vishsaggi]

What is the error you are getting? You are using Unqualify * without Qualify * which table you are trying to qualify?

May be try using Max Date in your SQL preceding load like below and try?

UNQUALIFY *;
FACT_ALM_DEFECT_TGT_PROD_RELEASE_DATE:
LOAD  "DEFECT_ID",
Max("TGT_PROD_RELEASE_DATE") as MAX_TGT_PROD_RELEASE_DATE

Group By "DEFECT_ID";
SQL SELECT *
FROM "IT_DW".dbo."FACT_ALM_DEFECT_TGT_PROD_RELEASE_DATE";

TGT_PROD_MasterCalendar:
  LOAD date(MAX_TGT_PROD_RELEASE_DATE, 'MM/DD/YYYY') as TGT_PROD_RELEASE_DATE,
  date(MAX_TGT_PROD_RELEASE_DATE, 'MMMYYYY') as TP_MY,
  Year(MAX_TGT_PROD_RELEASE_DATE) as TP_Year,
  Month(MAX_TGT_PROD_RELEASE_DATE) as TP_Month,
  QuarterStart(MAX_TGT_PROD_RELEASE_DATE) as TP_QTR,
  Day(MAX_TGT_PROD_RELEASE_DATE) as TP_Day;
  LOAD DISTINCT MAX_TGT_PROD_RELEASE_DATE
Resident FACT_ALM_DEFECT_TGT_PROD_RELEASE_DATE;