Calendar Table

[neerajthakur]

Temp:
Load
min("Invoice Date") as MinimumInvDate,
max("Invoice Date") as MaximumInvDate
Resident SalesDetails;
Load
min("CL_Key") as MinBudgetDate,
max("CL_Key") as MaxBudgetDates
Resident Budget;
Load
min("Actual Delivery Date") as MinDelDate,
max("Actual Delivery Date") as MaxDelDate
Resident SalesDetails;

LET varMinDate = Num(Peek('MinimumDate', 0,'Temp'));
LET varMaxDate = Num(Peek('MaximumDate', 0,'Temp'));
LET varToday = num(today());

Drop Table Temp;

//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate) + RowNo() - 1 AS Num,
Date($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;

//*************** Master Calendar ***************
MasterCalendar:
LOAD
TempDate AS DateKey,
TempDate AS "Invoice Date",
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
inyeartodate(TempDate, $(varToday), 0) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(varToday), -1) * -1 AS LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

I want to select MinimumDate and MaximumDate from line 3-12  there are three different types of dates for min and max , I want to include these dates so that calendar table gives date across all three dimensions instead of just one.

@PrashantSangle  I hope you can help 

[rubenmarin]

Hi, you can try with:

LET varMinDate = Num(RangeMin(Peek('MinimumInvDate', 0,'Temp'),Peek('MinBudgetDate', 0,'Temp'),Peek('MinDelDate', 0,'Temp')));

LET varMaxDate= Num(RangeMax(Peek('MaximumInvDate', 0,'Temp'),Peek('MaxBudgetDates', 0,'Temp'),Peek('MaxDelDate', 0,'Temp')));

[PrashantSangle]

Hi

you can try method suggest by Ruben rangemin() and rangemax() work

Or

Concatenate those 3 table with same fieldname and again take resident of temp table named it as finalTemp to find min and max.

And use finalTemp in variable instead of Temp.

Regards,

Prashant

[neerajthakur]

Since there are no common field already present, should I rename them same and concatenate?

[neerajthakur]

Temp:
Load
min("Invoice Date") as MinInvDate,
max("Invoice Date") as MaxInvDate
Resident SalesDetails
Where not IsNull("Invoice Date")
Order By "Invoice Date" ASC;

Let MinDate1 = Num(Peek('MinInvDate',0,'Temp'));
Let MaxDate1 = Num(Peek('MaxInvDate',0,'Temp'));
Drop Table Temp;

Temp1:
Load
min("CL_Key") as MinBudgetDate,
max("CL_Key") as MaxBudgetDate
Resident Budget;

Let MinDate2 = Num(Peek('MinBudgetDate',0,'Temp1'));
Let MaxDate2 = Num(Peek('MaxBudgetDate',0,'Temp1'));
Drop Table Temp1;

Temp2:
Load
min("Actual Delivery Date") as MinDelDate,
max("Actual Delivery Date") as MaxDelDate
Resident SalesDetails;

Let MinDate3 = Num(Peek('MinDelDate',0,'Temp2'));
Let MaxDate3 = Num(Peek('MaxDelDate',0,'Temp2'));
Drop Table Temp2;

Let varMinDate = Num(RangeMin(Peek('MinInvDate',0,'Temp'),Peek('MinBudgetDate',0,'Temp1'),Peek('MinDelDate',0,'Temp2')));
Let varMaxDate = Num(RangeMax(Peek('MaxInvDate',0,'Temp'),Peek('MaxBudgetDate',0,'Temp1'),Peek('MaxDelDate',0,'Temp2')));

// LET varMinDate = Num(Peek('MinimumDate', 0,'Temp'));
// LET varMaxDate = Num(Peek('MaximumDate', 0,'Temp'));
// LET varToday = num(today());

//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate) + RowNo() - 1 AS Num,
Date($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;

//*************** Master Calendar ***************
MasterCalendar:
LOAD
TempDate AS DateKey,
TempDate AS "Invoice Date",
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
inyeartodate(TempDate, $(varToday), 0) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(varToday), -1) * -1 AS LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

I have tried this after initial question, in this I am getting Autogenerate Negative Error

[PrashantSangle]

Yes.

Temp:
Load
min("Invoice Date") as MinDate,
max("Invoice Date") as MaxDate
Resident SalesDetails;
Load
min("CL_Key") as MinDate,
max("CL_Key") as MaxDates
Resident Budget;
Load
min("Actual Delivery Date") as MinDate,
max("Actual Delivery Date") as MaxDate
Resident SalesDetails;

FinalTemp:

Load

Min("MinDate") as finalMinDate,

Max("MaxDate") as finalMaxDate,

Resident Temp;

Drop table Temp;

Regards,

Prashant Sangle

[neerajthakur]

I have tried this method also but getting an error.

[neerajthakur]

Temp:
Load
min("Invoice Date") as MinDate,
max("Invoice Date") as MaxDate
Resident SalesDetails;

Concatenate

Load
min("CL_Key") as MinDate,
max("CL_Key") as MaxDate
Resident Budget;

Concatenate

Load
min("Actual Delivery Date") as MinDate,
max("Actual Delivery Date") as MaxDate
Resident SalesDetails;

FinalTemp:
Load
min(MinDate) as varMinDate,
max(MaxDate) as varMaxDate
Resident Temp;

Drop Table Temp;

Error is Autogenerate negative count

[PrashantSangle]

• What is value you are getting varMinDate and varMaxDate variable?

[rubenmarin]

Hi, if you do a drop of each temp table, when you get to the rangemin and rangemax sentences the tabla has alredy been deleted,.

Try doing the drops after you assign the variable.