Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
cbaqir
Valued Contributor

Max Date and Calendar in Load script

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;

Tags (4)
2 Replies
jwjackso
Valued Contributor

Re: Max Date and Calendar in Load script

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
Esteemed Contributor III

Re: Max Date and Calendar in Load script

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;