topic Max Date and Calendar in Load script in QlikView

Max Date and Calendar in Load script

cbaqir — Mon, 17 Sep 2018 22:54:42 GMT

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;

Re: Max Date and Calendar in Load script

jwjackso — Mon, 17 Sep 2018 23:44:57 GMT

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?

Re: Max Date and Calendar in Load script

vishsaggi — Tue, 18 Sep 2018 02:56:00 GMT

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;