Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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?
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;