Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist
Specialist

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;

2 Replies
jwjackso
Specialist II
Specialist II

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
Champion III
Champion III

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;