topic Re: Min and Max Dates in Load Script in QlikView

Min and Max Dates in Load Script

tmumaw — Sat, 16 Nov 2024 03:25:06 GMT

Hi all,

I need to get the min and max dates for each year in my load script. Is there an easy way to do it? Here is a sample of my script.

Thanks

CurrentDate:
LOAD FISPD,
FISYR,
[Billing Date] as Date,
WEDAT,
WKNO
FROM [D:\Qlikview\New Sales\QVD\ZZZWEDAT DaveyTree v1.0.qvd] (qvd)
where [Billing Date] = '$(vToday)';

LET vFisYr = peek('FISYR', 0, 'CurrentDate');
LET vFisPd = peek('FISPD', 0, 'CurrentDate');
LET vWkNo = peek('WKNO', 0, 'CurrentDate');

DROP TABLE CurrentDate;

FiscalCalendar_TEMP:
LOAD Distinct
FISPD as DFISPD,
FISYR as DFISYR,
[Billing Date] as Date_ZWKDATE,
WEDAT as DWEDAT,
num(WEDAT, '####0') as WEDAT_Num,
WKNO as DWKNO,
WeekDay([Billing Date]) as Weekday,
if([Billing Date] <= '$(vToday)' AND FISPD <= '$(vFisPd)' AND WKNO <= '$(vWkNo)', 1) AS YTDFlag,
if([Billing Date] <= '$(vToday)' AND FISPD = '$(vFisPd)', 1) AS MTDFlag,
if([Billing Date] <= '$(vToday)' AND FISPD = '$(vFisPd)' AND WKNO = '$(vWkNo)', 1) AS WTDFlag,
if([Billing Date] <= '$(vPrevToday)' AND [Billing Date] >= '$(vRolling4)', 1) AS RTD4Flag,
if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling3)', 1) AS RTD3Flag,
if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling2)', 1) AS RTD2Flag,
if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling1)', 1) AS RTD1Flag
FROM [D:\Qlikview\New Sales\QVD\ZZZWEDAT DaveyTree v1.0.qvd] (qvd)
where FISYR >= '$(vPYear)' and FISYR <= '$(vCYear)'
;

Left Join FiscalCalendar_Temp:
LOAD * INLINE [DFISPD, DQTR, DMTH
001, Q1, Jan,
002, Q1, Feb,
003, Q1, Mar,
004, Q2, Apr,
005, Q2, May,
006, Q2, Jun,
007, Q3, Jul,
008, Q3, Aug,
009, Q3, Sep,
010, Q4, Oct,
011, Q4, Nov,
012, Q4, Dec
];

FiscalCalendar:
LOAD *,
DFISYR & ' / ' & DQTR as DQYR,
DQTR & ' / ' & DFISYR as DYRQ,
DFISYR & ' / ' & DFISPD as DFYPD,
Right(DFISYR,2) & '-' & DMTH as DYRMTH,
Min(Date_ZWKDATE) as MinDate,
Max(Date_ZWKDATE) as MaxDate
Resident FiscalCalendar_TEMP;

Drop Table FiscalCalendar_TEMP;

Exit SCRIPT;

Re: Min and Max Dates in Load Script

sunny_talwar — Fri, 31 May 2019 11:54:12 GMT

Min and max from which table? CurrentDate? What do you want to do with it once you have the min and max date? Store them in a table or variables?

Re: Min and Max Dates in Load Script

tmumaw — Fri, 31 May 2019 12:08:34 GMT

I would like to store them in the table. I'm trying to see how many employees I have active on my min date and how many I have active on my max date. Maybe it just a expression. Don't know which way is the best. Be nice to just us a calculation. I have created a calculated dimension which works

=Aggr(Min(Date(Date_ZWKDATE)),DFISYR)

=Aggr(Max(Date(Date_ZWKDATE)),DFISYR)

I would like to just create an expression if possible using this calculation.

Count( {<Reason=,Job=,[Profit Center]=,EmpDays = {1}>} DISTINCT(Date_ZWKDATE))

Re: Min and Max Dates in Load Script

sunny_talwar — Fri, 31 May 2019 12:11:58 GMT

What exactly are you hoping your expression to do? How is Min and Max date each year linked to your expression? Do you want the distinct count for only min and max dates for each DFISYR?

Re: Min and Max Dates in Load Script

tmumaw — Fri, 31 May 2019 12:47:48 GMT

Sonny,

What I am trying to do is get the number of active employees for 2015, 2016, 2017. For each year I need to get starting date and ending date. We work off of a 4/4/5 financial calendar. I need to know the number of employees for the starting date and ending date. Thinking maybe just a calculated expression. Having a problem getting this to work.

sum({<Reason=,Job=,[Profit Center]=>}Aggr(Min(Date(Date_ZWKDATE)), EmpCt_ZEMPCT ,DFISYR))

Re: Min and Max Dates in Load Script

sunny_talwar — Fri, 31 May 2019 12:51:57 GMT

Difficult to say much without more information... I don't really know the data that you have and the output you are getting vs the output you want. From past I know that you are not able to share a sample, unless that has changed, I don't think I will be able to offer much help here.

All the best.

Re: Min and Max Dates in Load Script

tmumaw — Fri, 31 May 2019 15:17:53 GMT

I can share my date file and script. Here is my script

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LET vToday = today();
LET vYear = year(vToday) + 1;
Let vCYear = year(vToday);
LET vPYear = vYear - 5;
LET vnumToday = num(today());
LET vRolling5 = makedate(vCYear - 5, month(vToday), 1); // rolling 5 years
LET vRolling4 = makedate(vCYear - 4, month(vToday), 1); // rolling 4 years
LET vRolling3 = makedate(vCYear - 3, month(vToday), 1); // rolling 3 years
LET vRolling2 = makedate(vCYear - 2, month(vToday), 1); // rolling 2 years
LET vRolling1 = makedate(vCYear - 1, month(vToday), 1); // rolling 1 years
LET vPrevToday = makedate(vCYear - 2, month(vToday), 1);

//2015 Start and End dates

//LET vSYear='20150104'; //SAP Format SQL 'YYYYMMDD
//LET vEYear='20160102'; //SAP Format SQL 'YYYYMMDD'

//2016 Start and End dates

//LET vSYear='20160104'; //SAP Format SQL 'YYYYMMDD
//LET vEYear='20161231'; //SAP Format SQL 'YYYYMMDD'

//2017 Start and End dates

//LET vSYear='20170101'; //SAP Format SQL 'YYYYMMDD
//LET vEYear='20171230'; //SAP Format SQL 'YYYYMMDD'

//2018 Start and End dates

//LET vSYear='20171231'; //SAP Format SQL 'YYYYMMDD
//LET vEYear='20181229'; //SAP Format SQL 'YYYYMMDD'

//2019 Start and End dates

//LET vSYear='20181230'; //SAP Format SQL 'YYYYMMDD
//LET vEYear='20191228'; //SAP Format SQL 'YYYYMMDD'

CurrentDate:
LOAD FISPD,
FISYR,
[Billing Date] as Date,
WEDAT,
WKNO
FROM [D:\Qlikview\New Sales\QVD\ZZZWEDAT DaveyTree v1.0.qvd] (qvd)
where [Billing Date] = '$(vToday)';

LET vFisYr = peek('FISYR', 0, 'CurrentDate');
LET vFisPd = peek('FISPD', 0, 'CurrentDate');
LET vWkNo = peek('WKNO', 0, 'CurrentDate');

DROP TABLE CurrentDate;

FiscalCalendar_TEMP:
LOAD Distinct
FISYR as DFISYR,
FISPD as DFISPD,
WKNO as DWKNO,
WEDAT as DWEDAT,
num(WEDAT, '####0') as WEDAT_Num,
num([Billing Date], '####0') as BillingDateNum,
WeekDay([Billing Date]) as Weekday,
[Billing Date] as Date_ZWKDATE,
if([Billing Date] <= '$(vToday)' AND FISPD <= '$(vFisPd)' AND WKNO <= '$(vWkNo)', 1) AS YTDFlag,
if([Billing Date] <= '$(vToday)' AND FISPD = '$(vFisPd)', 1) AS MTDFlag,
if([Billing Date] <= '$(vToday)' AND FISPD = '$(vFisPd)' AND WKNO = '$(vWkNo)', 1) AS WTDFlag,
if([Billing Date] <= '$(vPrevToday)' AND [Billing Date] >= '$(vRolling4)', 1) AS RTD4Flag,
if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling3)', 1) AS RTD3Flag,
if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling2)', 1) AS RTD2Flag,
if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling1)', 1) AS RTD1Flag
FROM [D:\Qlikview\New Sales\QVD\ZZZWEDAT DaveyTree v1.0.qvd] (qvd)
where FISYR >= '$(vPYear)' and FISYR <= '$(vCYear)'
;
exit SCRIPT;
Left Join FiscalCalendar_Temp:
LOAD * INLINE [DFISPD, DQTR, DMTH
001, Q1, Jan,
002, Q1, Feb,
003, Q1, Mar,
004, Q2, Apr,
005, Q2, May,
006, Q2, Jun,
007, Q3, Jul,
008, Q3, Aug,
009, Q3, Sep,
010, Q4, Oct,
011, Q4, Nov,
012, Q4, Dec
];

FiscalCalendar:
LOAD *,
DFISYR & ' / ' & DQTR as DQYR,
DQTR & ' / ' & DFISYR as DYRQ,
DFISYR & ' / ' & DFISPD as DFYPD,
Right(DFISYR,2) & '-' & DMTH as DYRMTH,
Min(BillingDateNum) as MinDate,
Max(BillingDateNum) as MaxDate
Resident FiscalCalendar_TEMP;

Drop Table FiscalCalendar_TEMP;

Exit SCRIPT;

Re: Min and Max Dates in Load Script

tmumaw — Fri, 31 May 2019 15:20:43 GMT

Forgot to attach the data